SUBSTITUTE

What it does

SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(text, old_text, new_text, [instance_num]) returns text with occurrences of old_text rewritten as new_text.

Two rules define it, and both are documented:

1. old_text is matched by content, literally. The match is case sensitive and there is no wildcard language — a * in old_text is an asterisk.
2. Without instance_num, every occurrence is replaced. With instance_num, only that one occurrence is. Microsoft states this directly: if you specify instance_num, only that instance of old_text is replaced; otherwise every occurrence in text is changed.

SUBSTITUTE never fails to find something. If old_text does not occur in text, the result is text unchanged — not an error, not an empty string. This is the opposite of FIND and SEARCH, which raise #VALUE! when the target is absent, and it is why SUBSTITUTE is safe to apply speculatively while a SEARCH needs an IFERROR guard.

Note the case rule carefully, because it points the wrong way from most readers' intuition: SEARCH — the finding function most people reach for — is case-insensitive, while SUBSTITUTE — the editing function — is case-sensitive. A formula that tests with SEARCH and edits with SUBSTITUTE is applying two different matching rules to the same string, and will happily confirm that a substring is present and then decline to replace it.

Arguments

instance_num is the position most often misread. It is an occurrence index, not a character position and not a count of replacements to make. instance_num of 2 replaces the second occurrence and leaves the first and third alone.

instance_num is a numeric slot subject to ordinary to-number coercion; the other three are text slots. See Coercion and lifting.

Try it

Quirks

SUBSTITUTE versus REPLACE

The most-confused pair in the text category. Microsoft draws the line 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 absence of a SUBSTITUTEB is itself informative: by-content editing never needs a byte coordinate, so the DBCS variant that the positional family requires has no counterpart here. Under DBCS, SUBSTITUTE is generally the safer of the two for exactly that reason.

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

Result and edge cases

Returns Text.

Documented: the instance_num semantics above, and the by-content matching rule. Microsoft's page does not enumerate boundary conditions, so the following are open rather than settled:

• Empty old_text. SUBSTITUTE("abc", "", "X") has no obviously correct answer — an empty match occurs everywhere. Unverified.
• Empty new_text. Deletion, by the obvious reading, and the idiomatic way to strip a substring. Not stated on the page.
• instance_num greater than the number of occurrences, or zero, or negative. Unchanged input and #VALUE! are both plausible for the first; the last two are unaddressed.
• Overlapping occurrences, as in SUBSTITUTE("aaa", "aa", "X"). Left-to-right non-overlapping scanning is the usual convention and is not documented here.
• Whether replacement is re-scanned. If new_text contains old_text, a naive implementation can loop. Single-pass scanning is the sane behaviour and is not stated.

Errors

Microsoft's SUBSTITUTE page carries no error table. The reachable errors are the shared ones: an error value in any argument propagates, non-numeric text in instance_num surfaces #VALUE! under the shared coercion rules, and a result exceeding the 32,767 code-unit text cap is a candidate — the Handbook's value-universe chapter records #VALUE! for the over-cap formula path observed with REPT, but whether SUBSTITUTE publishes the same way is unverified.

SUBSTITUTE can grow its input without limit — replacing "a" with a long string in a long text — so the cap is genuinely reachable, not theoretical.

How to implement

Notes for implementers

Scanning must be single-pass and left-to-right, with the scan position advancing past new_text rather than re-entering it. Otherwise SUBSTITUTE("a", "a", "aa") does not terminate.

instance_num counting has to be done on the original occurrences, established by the same non-overlapping left-to-right scan. Counting occurrences in a partially rewritten string gives different answers.

Matching is over UTF-16 code units. That makes the match byte-alignment-free but also means an old_text that is half a surrogate pair can match, which is a state the value-universe chapter records as reachable.

Rust code

26 substitute_fn "SUBSTITUTE" (3, 4) [V "text", V "old_text", V "new_text", V "instance_num"] |s, a, sv| {
    let text = text_arg(s, a, 0)?;
    let old = text_arg(s, a, 1)?;
    let new = text_arg(s, a, 2)?;
    let instance = match a.get(3) {
        None => None,
        Some(value) if value.is_missing() => None,
        Some(_) => {
            let n = num(s, a, 3)?;
            Some(k::positive(n).map_err(Exit::Error)?)
        }
    };
    capped(k::substitute(&text, &old, &new, instance), WorksheetErrorCode::Value)
}

pub(crate) fn substitute(
    text: &ExcelText,
    old_text: &ExcelText,
    new_text: &ExcelText,
    instance_num: Option<usize>,
) -> ExcelText {
    let hay = text.utf16_code_units();
    let needle = old_text.utf16_code_units();
    let replacement = new_text.utf16_code_units();
    if needle.is_empty() {
        return text.clone();
    }
    let mut out = Vec::with_capacity(hay.len());
    let mut index = 0usize;
    let mut occurrence = 0usize;
    while index < hay.len() {
        if index + needle.len() <= hay.len() && &hay[index..index + needle.len()] == needle {
            occurrence += 1;
            if instance_num.is_none() || instance_num == Some(occurrence) {
                out.extend_from_slice(replacement);
                index += needle.len();
                continue;
            }
        }
        out.push(hay[index]);
        index += 1;
    }
    ExcelText::from_utf16_code_units(out)
}

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.

Alternatives

Relationships

• REPLACE — the by-position counterpart. See the comparison above.
• SEARCH and FIND — the locators. Both differ from SUBSTITUTE on the matching axis: SEARCH is case-insensitive with wildcards, FIND is case-sensitive and literal, SUBSTITUTE is case-sensitive and literal. FIND is the locator whose matching rule actually agrees with SUBSTITUTE's.
• REGEXREPLACE is the modern general-purpose alternative, with a real pattern language, case options and capture groups. SUBSTITUTE is not superseded — literal replacement is still its own job, and it is the cheaper and clearer tool when that is all you need.
• TRIM and CLEAN are the fixed-purpose cleaners; SUBSTITUTE(text, CHAR(160), " ") is the standard idiom for the non-breaking spaces that TRIM does not touch, which is probably this function's single most common real-world use.
• LEN(text) - LEN(SUBSTITUTE(text, x, "")) is the classic occurrence-counting idiom, and it works precisely because SUBSTITUTE returns the input unchanged when there is nothing to do.

Named variants

No named variants are recorded yet.