SUBSTITUTE in WPS Spreadsheet: Replace Matching Text or One Occurrence
Replace specified text in WPS Spreadsheet with all-occurrence and numbered-occurrence examples, cleanup and length checks.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: SUBSTITUTE replaces specified text within a string. It can replace every occurrence or only the occurrence number you supply. =SUBSTITUTE("red-red-red","red","blue",2) is expected to return red-blue-red.
Availability and version history
- Introduced: documented by 2019-05-14 in the official WPS lesson; original introduction is not established.
- Availability: WPS Office for Windows Personal; numeric release not identified in the lesson. This is documentation evidence, not an installed-build guarantee.
- Scope: the exact supported build range is unverified. The cited Windows lesson does not establish equivalent availability in other WPS clients.
- Checked: October 14, 2026 against the function-specific lesson and vendor catalogue. Examples were independently calculated; no new native WPS test was performed.
Syntax and the optional occurrence
=SUBSTITUTE(text, old_text, new_text, [instance_num])
text: source string or reference.old_text: the text to replace.new_text: replacement text, including""when deleting.instance_num: optional occurrence number, counted from the beginning.
Although the official lesson’s syntax line does not use brackets, its explanation expressly says that omitting the fourth argument replaces all occurrences. This guide displays that optionality explicitly. The selected examples use exact literal tokens and positive whole-number occurrence values.
Example 1: replace all separators
Enter these strings in A2:A4:
| Cell | Source |
|---|---|
| A2 | north-east-07 |
| A3 | south-west-12 |
| A4 | central-04 |
In B2 enter and fill down:
=SUBSTITUTE(A2,"-","/")
Expected B2:B4: north/east/07, south/west/12, central/04.
With no occurrence argument, every matching hyphen is replaced. The function works within each source string; it does not search an entire worksheet or edit the original cells.
Example 2: replace only the second occurrence
Use A2 from the first example:
=SUBSTITUTE(A2,"-","/",2)
Expected result: north-east/07. The first hyphen remains unchanged. For =SUBSTITUTE("A-A-A","A","B",2), the expected result is A-B-A.
An occurrence number identifies a match, not a character position. If the match text has several characters, each complete occurrence still counts as one. For example:
=SUBSTITUTE("cat/cat/cat","cat","fox",3)
Expected result: cat/cat/fox. To edit a known character position instead, use REPLACE.
Example 3: remove a literal separator
=SUBSTITUTE("AB-004-CA","-","")
Expected result: AB004CA. The empty string "" removes matching text. It is not a space and does not turn the result into a number.
This can help produce a comparison key when the source system explicitly treats separators as cosmetic. Preserve the original identifier, because removing separators can make distinct original strings collide: AB-C and A-BC both become ABC.
Advanced example: apply two controlled cleanup rules
Put AB - 004 - CA in E2. If both hyphens and ordinary spaces are removable under the field’s rules, use:
=SUBSTITUTE(SUBSTITUTE(E2,"-","")," ","")
Expected result: AB004CA. The inner function removes hyphens; the outer function removes ordinary spaces. This is a literal two-step transformation, not a general invisible-character cleaner.
For review, first put the inner expression in F2 and inspect AB 004 CA, then use G2 to remove spaces from F2. Separate helper columns make it easier to catch a cleanup rule that damages valid values.
Advanced example: count a one-character separator
With north-east-07 in A2:
=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))
Expected result: 2. The original has 13 characters; removing both hyphens leaves 11. Each removed match is one character, so the length difference is the occurrence count.
For a longer literal token, divide the removed-character count by that token’s length, provided the intended matching rule is clear. Do not use this technique to infer arbitrary overlapping matches without validating that case separately.
Troubleshooting and safe use
When nothing changes, compare the actual source with old_text, including punctuation and spacing. Use the exact spelling you intend to replace; this guide does not claim a case-normalization or wildcard-expansion feature. A broader cleanup rule should be a deliberate separate step.
Check the fourth argument if only one replacement was expected. Check the third argument if text disappeared. Retain source data and validate a few examples before filling down. A formula result that looks tidy can still be wrong if the replaced token carries meaning.
References and verification
- Official WPS SUBSTITUTE lesson, published 2019-05-14 (Chinese; Windows Personal scope).
- WPS SUBSTITUTE documentation catalogue.
The lesson establishes the function-specific behavior discussed here. The catalogue confirms its listed identity, not an exact current build range. Expected outputs use original examples checked independently; native execution and broader client compatibility remain unverified.