REPLACE in WPS Spreadsheet: Edit a Known Character Span

• Introduced: documented by 2020-04-09 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.

=REPLACE(old_text, start_num, num_chars, new_text)

All four arguments are required. old_text is the source; start_num is the first position to replace, counting from one; num_chars is the number of original characters removed; new_text is what takes their place.

The replacement can be a different length from the removed text. The unchanged prefix and suffix are retained around it. The examples use ASCII strings with visible separators to make the positions reproducible.

Enter A1:A4:

In B2 enter and fill down:

=REPLACE(A2,4,4,"XXXX")

Expected B2:B4: AB-XXXX-Z, CD-XXXX-Q, EF-XXXX-R.

The first two letters occupy positions 1–2, the first hyphen is position 3, and the four digits occupy positions 4–7. REPLACE does not verify that those four characters are digits; it replaces that span regardless of its contents.

This creates a display value. It does not securely erase the original code from the workbook. The source column, formula references, hidden sheets or file history may still contain it. Use a separate reviewed export if a real disclosure-minimization workflow is needed.

=REPLACE("AB-1234-Z",4,4,"?")

Expected result: AB-?-Z. Four original characters are replaced with one character. The result is shorter than the source.

For a longer replacement:

=REPLACE("AB-1234-Z",4,4,"PENDING")

Expected result: AB-PENDING-Z. The replacement’s length does not have to equal num_chars. A downstream fixed-width parser may therefore need a different rule after this transformation.

=REPLACE("BOX-0042",4,1,"")

Expected result: BOX0042. The fourth character, a hyphen, is replaced with empty text. The leading zeros remain part of the source string.

If the hyphen could occur at different positions, a fixed position is fragile. SUBSTITUTE is more suitable when the requirement is to replace a matching text token instead of a predetermined span.

Put AA-1234-US in E2. Suppose the prefix should become DEPOT and the suffix should become CA. Replacing the shorter prefix first shifts the original suffix positions, so use a helper workflow that edits the rightmost field first:

=REPLACE(E2,9,2,"CA")

Expected F2: AA-1234-CA. Then:

=REPLACE(F2,1,2,"DEPOT")

Expected G2: DEPOT-1234-CA. Editing from right to left preserves the original position of the earlier field. The nested equivalent is:

=REPLACE(REPLACE(E2,9,2,"CA"),1,2,"DEPOT")

Use helper cells while developing the formula. The nested expression is compact, but a mistaken order or off-by-one position is easier to diagnose when intermediate text is visible.

For the original AB-1234-Z schema, use LEN to check that each source contains nine characters before applying the fixed positions. Length is a preliminary check, not proof of valid letters, digits or separators.

Use positive whole-number starting positions and nonnegative whole-number character counts. If a formula returns an error, inspect those arguments and the source references first. The cited lesson does not provide an exhaustive WPS error-code table, so this guide does not assign unverified error codes to every invalid case.

Keep quotes around literal replacement text. A missing quote can turn a label into an invalid name. Preserve the original column and test at least one normal record, one malformed record and one replacement that changes length before filling a whole dataset.

MID extracts a span without replacing it. SUBSTITUTE replaces matching text. Choosing between these functions starts with whether the task is defined by positions, by literal matches or by extraction rather than editing.

• Official WPS REPLACE lesson, published 2020-04-09 (Chinese; Windows Personal scope).
• WPS REPLACE 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.