SUBSTITUTES in WPS Spreadsheet: Replace Several Nonoverlapping Labels

=SUBSTITUTES(string,old_string,new_string,[replacement_index])

Supply the source text range, the old-text mapping range and the equally sized new-text mapping range. The live example omits the fourth field, so it is not required for the demonstrated workflow. Its occurrence-selection semantics are not established here. The dialog also mentions omitted replacement text, but these recipes always supply explicit new strings and do not test deletion.

Enter A1=Red, Blue, A2=Green, Red, A3=Blue, Green. Enter D1=Red, D2=Blue, E1=Amber, E2=Violet. In G1, with G1:G3 clear, enter =SUBSTITUTES(A1:A3,D1:D2,E1:E2).

Expected vertical output: Amber, Violet; Green, Amber; Violet, Green. Mapping row 1 pairs Red with Amber; row 2 pairs Blue with Violet. Green has no matching mapping and stays unchanged. Each old label occurs at most once per cell, avoiding unverified repeated-occurrence behavior.

For a four-rule example, use A5=Cat, Dog, A6=Bird, Fish, A7=Cat, Fish. Put Cat, Dog, Bird, Fish in D5:D8 and Lynx, Wolf, Robin, Trout in E5:E8. With G5:G7 clear, =SUBSTITUTES(A5:A7,D5:D8,E5:E8) in G5 should produce Lynx, Wolf; Robin, Trout; Lynx, Trout. None of the replacement strings matches another old label, so this does not assume simultaneous versus cascading replacement.

At 58.663s the live dialog shows =SUBSTITUTES(C26:C31,G26:G29,H26:H29), with six input strings, four mapping rows and the fourth argument blank. The first result preview preserves the first name and replaces the two mapped substrings that follow it. The four old-text rows are aligned with four new-text rows. At 66.991s the six-row result is visible. The examples above use short labels to make the mapping easier to follow.

Replacement order, overlapping old strings, repeated occurrences, case sensitivity, unmatched mapping dimensions and the fourth argument are not established by these examples. The dialog describes substring replacement, so do not assume whole-word matching.

If the function is not recognized, confirm its availability in the intended WPS installation before changing the formula or substituting a similarly named function. For array outputs, enter the formula in an unused area and reserve the stated output rectangle. Exact blocked-output errors are not established here.

The official WPS SUBSTITUTES lesson shows a Chinese-language WPS spreadsheet interface. The review on October 19, 2026 did not establish a numeric build, exact edition, operating system, license requirement or introduction date. Historical demonstration does not prove present support across desktop, web and mobile editions.

The examples above have independently checked expected results. They have not been executed in an installed WPS build.

For a single old-text/new-text pair, see SUBSTITUTE.

• Official WPS SUBSTITUTES lesson
• Official lesson video

The timestamps refer to the linked WPS video. Behavior beyond the examples, including support in your installed version, should be checked before production use.