REGEXREPLACE
Replace the parts of a string that match a regular expression — every match, or just one — with capture groups available as $1, $2.
Syntax
REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])| Argument | Required | Description |
|---|---|---|
text | Required | The text to change. |
pattern | Required | A regular expression. |
replacement | Required | The text to put in place of each match. $1, $2 insert capture groups. |
occurrence | Optional | 0 (the default) replaces every match; n replaces only the nth; -n the nth from the end. |
case_sensitivity | Optional | 0 (the default) for case-sensitive matching, 1 to ignore case. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=REGEXREPLACE(A2,"\D","") | 5551234567 | Remove everything that is not a digit. |
=REGEXREPLACE(A3,"([A-Z][a-z]+)([A-Z][a-z]+)","$2, $1") | Brown, Sonia | Capture groups reordered with $1 and $2. |
=REGEXREPLACE("a1b2c3","\d","*",2) | a1b*c3 | Only the second match. |
=REGEXREPLACE("a1b2c3","\d","*",-1) | a1b2c* | Only the last match. |
=REGEXREPLACE(" too many spaces ","\s+"," ") | too many spaces | Runs of whitespace collapsed to one space. |
Cleaning imported text
REGEXREPLACE handles the clean-ups SUBSTITUTE
cannot, because it matches kinds of characters rather than one fixed string:
strip every non-digit from a phone number, remove trailing punctuation, collapse
runs of spaces.
Capture groups in the replacement
$1 inserts what the first parenthesised group matched, $2 the second. $1x
means group 1 followed by “x”, as in Excel. A $ that is not followed by a
group number is kept as a literal dollar sign, and $$ is a single $.
One occurrence or all
By default every match is replaced. A positive occurrence replaces only that
match, counting from the start; a negative one counts from the end. An
occurrence past the number of matches leaves the text unchanged.
Excel compatibility
Matches Excel's REGEXREPLACE, including occurrence counted from the end with a negative number and $n for capture groups. Excel uses PCRE2; lookaround and backreferences are not supported here and return #VALUE!.