REGEXREPLACE

Swap out parts of a text string that match a regular expression pattern. Great for cleaning data, reformatting values, or stripping unwanted characters.

Text
|
Excel 365 only
|
Google Sheets Supported

Spreadsheet editor

Syntax

=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) Returns: Text

Arguments

Argument Required Description
text Yes The text you want to change. Type it in quotation marks or point to a cell that holds the text.
pattern Yes The regular expression that describes what you want to find and replace. Excel uses the PCRE2 style of regex.
replacement Yes The text to put in place of each match. Use an empty string "" to remove matches, or backreferences like $1 and $2 to reuse the parts you wrapped in parentheses.
occurrence No Which match to replace. 0 (the default) replaces every match. A positive number counts from the start, so 1 changes the first match. A negative number counts from the end, so -1 changes the last match.
case_sensitivity No Set to 0 (the default) to treat uppercase and lowercase as different, or 1 to ignore case while matching.

About

REGEXREPLACE finds every part of your text that matches a regular expression and swaps it for new text you choose. You give it the text, a pattern that describes what to find, and the replacement to drop in. This makes it a fast way to clean up messy data, reformat values into a tidy shape, or remove characters you do not want, all in one formula.

Use it when a plain find-and-replace is not flexible enough. SUBSTITUTE and REPLACE are perfect when you know the exact text or position, but REGEXREPLACE wins when the thing you want to change keeps changing shape, like phone numbers in different formats, extra spaces, or codes that follow a pattern. You can even reuse the matched pieces in your replacement with backreferences such as $1 and $2, which lets you reorder or reformat text on the fly.

REGEXREPLACE works in Microsoft 365 and uses the PCRE2 flavor of regex, the same style you may know from many programming languages. By default it replaces every match, but the occurrence argument lets you target a single match, and case_sensitivity lets you decide whether uppercase and lowercase count as the same. It always returns text, so wrap the result in VALUE when you need a real number. To pull matches out instead of replacing them, use REGEXEXTRACT.

Examples

Reformat with backreferences

Turn a plain 10-digit number into a tidy (123) 456-7890 layout. Wrap each part of the pattern in parentheses, then drop $1, $2, and $3 wherever you want them. Edit a number and watch the format follow.

Spreadsheet editor

Cleaned text won't add up

Strip the symbols from a price and notice the result is still text from the "Number stored as text" warning and the SUM in column B returns 0. Column C wraps the same cleanup in VALUE, so those results are real numbers and the total works.

Spreadsheet editor

Collapse messy spacing

See how \s+ squeezes every run of spaces into one, then notice it leaves the spaces at the ends untouched. Wrap it in TRIM to clean those off and get a tidy label.

Spreadsheet editor

Watch out for

⚠

It replaces every match by default

Many people expect the formula to change only the first match, but with no occurrence argument REGEXREPLACE swaps every match in the text.

→ Pass an occurrence number to target one match. Use a positive number from the left, like =REGEXREPLACE(A2, "-", " ", 1) for the first dash, or a negative number from the right, like -1 for the last dash.

⚠

Backreferences use a dollar sign

Trying to reuse a captured group with \1 or %1 in the replacement does not work, so the result comes back with literal text instead of the match.

→ Refer to capturing groups with a dollar sign and a number, like $1 and $2, for example =REGEXREPLACE(A2, "(\d+)-(\d+)", "$2-$1").

⚠

The result is text, not a number

REGEXREPLACE always returns text, so a cleaned-up value like 12345 will not add up or sort as a number.

→ Convert it with VALUE, like =VALUE(REGEXREPLACE(A2, "\D", "")), to get a real number back.

⚠

Not available in older Excel

REGEXREPLACE only exists in Microsoft 365, so the formula returns a #NAME? error in Excel 2021 and earlier.

→ On older versions, rebuild the logic with SUBSTITUTE for exact swaps or REPLACE for changes by position.

Tips & notes

REGEXREPLACE uses the PCRE2 style of regex, so patterns you know from languages like Python or JavaScript usually work the same way. Set case_sensitivity to 1 when you want to ignore case. To pull matching text out instead of replacing it, use REGEXEXTRACT, and to check whether a pattern matches at all, REGEXTEST is the companion function. When you only need to swap exact text, SUBSTITUTE is the simpler choice.

Common questions

How do I remove characters instead of replacing them?

Use an empty string for the replacement. For example, =REGEXREPLACE(A2, "[^0-9]", "") deletes every character that is not a digit and keeps only the numbers.

How do I change only one match instead of all of them?

Set the occurrence argument to the match you want. =REGEXREPLACE(A2, "o", "0", 2) changes the second "o" from the start, while -1 would change the last match.

Can I make the match ignore uppercase and lowercase?

Yes. Set the case_sensitivity argument to 1, like =REGEXREPLACE(A2, "error", "ok", 0, 1), so it matches "Error", "ERROR", and "error" alike.

Does REGEXREPLACE work in Google Sheets?

Yes. Google Sheets has REGEXREPLACE too, with the syntax REGEXREPLACE(text, regular_expression, replacement). It replaces every match and does not support the occurrence or case_sensitivity arguments.