REGEXREPLACE

What it does

REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

REGEXREPLACE finds matches of a regular expression in a string and substitutes replacement text for them, returning the rewritten string.

The operation is: scan text for non-overlapping matches of pattern from the left; for the match or matches selected by the occurrence argument, splice replacement in place of the matched substring; leave everything else untouched; return the result. The unmatched parts of the string pass through verbatim, which is what distinguishes a replace from an extract.

The dialect is PCRE2, per the family statement on Microsoft's REGEXTEST page.

What the same replay does pin, as an upstream observation, is the selective form: REGEXREPLACE("abc123def", "[a-z]+", "X", 2) -> abc123X — a positive occurrence of 2 rewriting the second match and leaving the first alone.

Arguments

REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

Try it

Quirks

Result and edge cases

The return kind is Text.

• No match returns the input unchanged. This is the natural reading and is how every replace function in common use behaves, but note that it differs from REGEXEXTRACT, which returns #N/A on no match. If your formula needs to know whether anything was replaced, REGEXREPLACE will not tell you — pair it with REGEXTEST.
• Zero-width matches need an advance rule. A pattern that can match the empty string, replaced globally, would otherwise loop or insert the replacement between every character. Which of those Excel does is unprobed.
• The result can grow past the text cap. Replacing a short pattern with a long replacement in a long string is the easiest way in the text family to exceed 32,767 code units. The cap and its two enforcement paths are described in the value universe.
• Non-text arguments coerce to text by the ordinary rules in coercion and lifting. The generated battery rows that pass a number in all three positions are coercion artefacts, not regex behaviour.
• Errors propagate ahead of matching.
• An empty referenced cell delivers Empty, distinct from an omitted argument.
• Array arguments are not asserted here.

Errors

Microsoft's page is the authority on documented error conditions; it was not retrievable while this entry was written, so this section states bases rather than quoting it.

How to implement

Notes for implementers

• Decide and document the default-occurrence question. It is the single most visible behaviour of the function and the sources consulted here do not settle it. Do not copy a default from another language's replace.
• Decide whether replacement is a template or a literal, and be loud about it. If it is a template, $ becomes a metacharacter and every user string containing a dollar sign is a latent bug; if it is literal, capture-group substitution is impossible and users will ask why.
• Advance past zero-width matches or the global replace does not terminate.
• Splice, do not rebuild by repeated search. Repeatedly searching the partially rewritten string can match text that the replacement itself introduced — a classic and silent defect.
• The escape set is part of the contract. The upstream record's live-Excel comparison lists as admitted, on the build it tested: \d \D \w \W \s \S; \A \Z \z \b \B; \n \t \r \f \v \e \h; and the escaped literals \. \* \+ \? \[ \] \( \) \| \^ \$ \/ \\; and lists unknown letter escapes such as \q \k \m \g \p \c \x \y \j \o as rejected. Upstream observation on one Excel build, not a Handbook claim.
• Check the result against the text cap and decide which failure you implement.
• Bound the match effort, or a pathological pattern hangs a recalculation.

Rust code

18 regexreplace_fn "REGEXREPLACE" (3, 5) [V "text", V "pattern", V "replacement", V "occurrence", V "case_sensitivity"] |s, a, sv| { regexreplace(s, a) }

fn regexreplace(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let text = text_arg(site, a, 0)?;
    let pattern = text_arg(site, a, 1)?;
    let replacement = text_arg(site, a, 2)?.to_string_lossy();
    if replacement.contains('$') || replacement.contains('\\') {
        // Group references in the replacement are not modelled.
        return Err(site.not_modelled(Some(2)));
    }
    let occurrence = if given(a, 3) { num(site, a, 3)?.trunc() } else { 0.0 };
    let sensitive = case_sensitive(site, a, 4)?;
    let (chars, found) = regex(site, &text, &pattern, sensitive, false)?;
    let selected: Vec<(usize, usize)> = if occurrence == 0.0 {
        found
    } else if occurrence > 0.0 {
        found.get(occurrence as usize - 1).copied().into_iter().collect()
    } else {
        let back = (-occurrence) as usize;
        found
            .len()
            .checked_sub(back)
            .and_then(|i| found.get(i).copied())
            .into_iter()
            .collect()
    };
    let mut out = String::new();
    let mut cursor = 0;
    for (start, end) in selected {
        out.extend(chars[cursor..start].iter());
        out.push_str(&replacement);
        cursor = end;
    }
    out.extend(chars[cursor..].iter());
    Ok(crate::catalogue::kit::text_value(&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

• REGEXTEST — the predicate. Use it to find out whether a replace would do anything.
• REGEXEXTRACT — the extractor. Same engine; returns the match instead of rewriting around it.
• SUBSTITUTE — the direct predecessor and still the right tool for literal text. It takes an instance_num in the same spirit as occurrence, and upstream records its rules for the current baseline: an empty old_text leaves the source unchanged, and instance_num < 1 is #VALUE!. SUBSTITUTE is not superseded and is clearer for any non-pattern replacement.
• REPLACE — positional replacement by start and length; a different job entirely despite the near-identical name. Readers confuse REPLACE and REGEXREPLACE constantly; the first takes character positions, the second takes a pattern.
• TRIM — worth mentioning because REGEXREPLACE(x, "\s+", " ") is the obvious "trim with a wider whitespace set" idiom, and is the standard workaround for TRIM not touching non-breaking spaces. Whether Excel's \s includes the non-breaking space is itself unprobed.
• TEXTJOIN, TEXTSPLIT — the delimiter tools, usually a better fit than a regex when the structure really is a separator.

Named variants

No named variants are recorded yet.