REGEXREPLACE function
REGEXREPLACE replaces anything in text that matches a pattern, for example stripping every non-digit from a phone number. It handles cleanup jobs SUBSTITUTE cannot, and replaces all matches unless you set an occurrence.
Example
Read it as a sentence: replace each run of digits in a1b22 with #, which gives a#b#.
A
B
C
D
1
a#b#
2
3
Syntax
REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
Arguments in [square brackets] are optional.
-
text(required) - The text to modify.
-
pattern(required) - The regular expression to find.
-
replacement(required) - Text to put in place; can reference capture groups like $1.
-
occurrence(optional) - Which match to replace; 0 means all (default), negatives count from the end.
-
case_sensitivity(optional) - 0 case-sensitive (default), 1 ignore case.
Returns: The text with matches replaced.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=REGEXREPLACE("a1b22","\d+","#") | a#b# | All occurrences |
=REGEXREPLACE("a1b22","\d+","#",2) | a1b# | Second occurrence |
=REGEXREPLACE("a1b22","\d+","#",-1) | a1b# | Last occurrence |
=REGEXREPLACE("John Smith","(\w+) (\w+)","$2, $1") | Smith, John | Backreferences |
=REGEXREPLACE("ABC","b","x",,1) | AxC | Case-insensitive |
=REGEXREPLACE("abc","z","x") | abc | No match |
=REGEXREPLACE("abc","(","x") | #VALUE! | Invalid pattern |
Watch out for
- Special characters in the pattern need escaping.
- Replaces all matches unless you set occurrence.
Where it works
- Excel
- REGEXREPLACE is only in Excel for Microsoft 365 and Excel for the web. Recent Microsoft 365 builds only.
- Google Sheets
- Same name, but behaves differently. Sheets REGEXREPLACE takes only text, regular_expression, replacement, and replaces all matches.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .