Practice free

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#.

Practice this in the app

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.

FormulaExcel showsCase
=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 .