REPLACE

What it does

REPLACE(old_text, start_num, num_chars, new_text)

REPLACE(old_text, start_num, num_chars, new_text) returns old_text with the window of num_chars characters beginning at position start_num removed and new_text put in its place. Positions are 1-based.

Equivalently, in terms of the slicing functions:

REPLACE(t, s, k, new)  =  LEFT(t, s - 1) & new & MID(t, s + k, LEN(t))

Two consequences of that identity are worth reading off it directly, because they are the two things people get wrong about this function:

1. REPLACE does not look at what it is replacing. It never inspects the characters in the window. It cannot fail to find anything, because it does not search.
2. The result length is not the input length. new_text need not be num_chars long. With num_chars of 0, REPLACE inserts without deleting; with new_text empty, it deletes without inserting. These are not tricks — they fall straight out of the definition, and they are the idiomatic way to do both operations.

REPLACEB is the byte-addressed sibling; see REPLACEB.

Arguments

All four are required — there is no "replace to the end" default and no optional argument.

start_num and num_chars are numeric slots subject to ordinary to-number coercion; see Coercion and lifting.

The commonly misread position is num_chars. It is a count of characters to remove from old_text, not an end position and not the length of new_text. REPLACE("abcdef", 2, 3, "XY") removes bcd and yields aXYef.

Try it

Quirks

Result and edge cases

Returns Text.

Microsoft's REPLACE page states the signature and the meaning of each argument but does not enumerate boundary behaviours the way the MID page does. The following are therefore expected rather than documented, and the Handbook has not checked any of them:

• start_num past the end of old_text — plausibly an append; unverified.
• num_chars running past the end of old_text — plausibly a clamp to the end; unverified.
• num_chars of 0 — insertion, as above; this follows from the definition but is not stated on the page.
• start_num less than 1 — the sibling functions document #VALUE! for this; REPLACE's page does not.

REPLACE versus SUBSTITUTE

These two are the most-confused pair in the text category, and the distinction is clean once stated. Microsoft states it on the SUBSTITUTE page itself:

Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

The one-line test: if your formula computes start_num with FIND or SEARCH, you probably wanted SUBSTITUTE. If your positions come from a fixed-width record layout, you wanted REPLACE.

See SUBSTITUTE for the other side of this pair.

Errors

Microsoft's REPLACE, REPLACEB page does not carry an error table. The errors reachable here are the shared ones: an error value in any argument propagates, and non-numeric text in start_num or num_chars surfaces #VALUE! under the shared coercion rules. A result exceeding the 32,767 code-unit text cap is the other candidate — the Handbook's value-universe chapter records #VALUE! for the over-cap formula path observed with REPT, but whether REPLACE publishes the same way is unverified.

How to implement

Notes for implementers

The three-piece concatenation identity above is the implementation, and the only subtleties are at its edges: what LEFT(t, s-1) does when s-1 exceeds LEN(t), and what MID(t, s+k, LEN(t)) does when s+k exceeds it. Both must be defined so that a window entirely past the end degrades gracefully rather than producing a #VALUE! from an intermediate step.

Because REPLACE can grow its input, it is one of the functions that can manufacture a string over the 32,767 code-unit cap from inputs that are each individually legal. Whatever the publication behaviour turns out to be, it has to be applied at the function's own boundary and not left to the cell.

Counting is in UTF-16 code units, consistent with LEN and MID. A REPLACE window can therefore split a surrogate pair, and the implementation has to survive producing ill-formed UTF-16 — the Handbook's value-universe chapter records that state as reachable in practice.

Rust code

24 replace_fn "REPLACE" (4, 4) [V "old_text", V "start_num", V "num_chars", V "new_text"] |s, a, sv| {
    replace(s, a, false)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Recorded variants

Alternatives

Relationships

• SUBSTITUTE — the by-content counterpart. See the section above.
• MID — the same (start_num, num_chars) addressing, reading instead of writing. MID extracts the window REPLACE overwrites.
• REPLACEB — the byte-addressed sibling, documented by Microsoft on the same page and published by the Handbook as a separate entry.
• FIND and SEARCH are what supplies start_num when the window is not known in advance. SEARCH is case-insensitive and takes wildcards; FIND is neither. Choosing between them changes what REPLACE overwrites.
• TEXTBEFORE / TEXTAFTER cover many REPLACE-with-a-computed-position formulas more directly.

Named variants

No named variants are recorded yet.