REGEXREPLACE function in Google Sheets

REGEXREPLACE replaces every part of a text that matches a pattern with other text.

SUBSTITUTE replaces one exact piece of text. REGEXREPLACE replaces everything that fits a pattern: all digits, all punctuation, any run of spaces. For cleaning data, it often does in one formula what would take five nested SUBSTITUTEs.

Syntax

=REGEXREPLACE(text, regular_expression, replacement)

Argument	What it is
`text`	The text to change.
`regular_expression`	The pattern to look for.
`replacement`	What to put in place of each match. Use "" to delete. $1, $2 stand for the bracketed groups of the pattern.

Examples

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=REGEXREPLACE(C2,"-","")
Result: PB101234
Every dash removed.
=REGEXREPLACE(C2,"\d","X")
Result: PB-XX-XXXX
Every digit replaced by X.
=REGEXREPLACE(C2,"\d{4}$","****")
Result: PB-10-****
Masking: only the four digits at the end are changed.
=REGEXREPLACE(D2,"[^0-9.]","")
Result: 1250
Everything that is not a digit or a dot removed from "1,250". The result is text.
=REGEXREPLACE(D5,"\D","")
Result: 12
\D means "not a digit": strips the unit from "12 kg".
=REGEXREPLACE(A5,"\s+"," ")
Result: Kabir Khan
Any run of spaces squeezed to one.
=REGEXREPLACE(A3,"(\w+) (\w+)","$2, $1")
Result: KUMAR, RAVI
Groups reused in the replacement: the two words swapped, with a comma.
=REGEXREPLACE(B2,"@.*","")
Result: asha.verma
The @ and everything after it removed.

How it works

Every match of the pattern in the text is replaced. There is no option to change only the first: all are changed. To target one place, make the pattern more specific, for instance with ^ for the start or $ for the end, as the third example does.

The patterns are those described on the REGEXMATCH page.

Deleting

An empty replacement, "", removes the matches. The most useful deletions:

To remove	Pattern
everything except digits	`\D` or `[^0-9]`
everything except letters	`[^A-Za-z]`
all spaces	`\s`
punctuation	`[[:punct:]]`
anything in brackets	`$.*?$`
leading and trailing spaces	`^\s+\|\s+$`

=REGEXREPLACE(A2, "\D", "") is the classic for phone numbers: it strips spaces, dashes, brackets and plus signs in one go, leaving only digits.

Reusing parts of the match

Brackets in the pattern capture pieces, and $1, $2 and so on put them back in the replacement. That lets you rearrange text.

Swap two words: pattern (\w+) (\w+), replacement $2 $1.
Turn 20261112 into a dated form: pattern (\d{4})(\d{2})(\d{2}), replacement $3-$2-$1 gives 12-11-2026.
Put brackets around an area code: pattern ^(\d{3})(\d+), replacement ($1) $2.

$0 stands for the whole match.

The result is text

Like all text functions, REGEXREPLACE returns text. After stripping everything but digits, wrap it in VALUE to get a number: =VALUE(REGEXREPLACE(A2, "[^0-9.]", "")).

And it needs text to start with. On a number, use TO_TEXT(A2) or A2&"".

Cleaning a whole column

With ARRAYFORMULA: =ARRAYFORMULA(IF(A2:A="", "", REGEXREPLACE(A2:A, "\s+", " "))).

For a one-time change to the data itself, the Find and replace box (Ctrl and H) has a tick box "Search using regular expressions", which uses the same patterns and changes the cells in place.

REGEXREPLACE or SUBSTITUTE?

One exact, known piece of text: SUBSTITUTE. It is simpler, and can change only the nth occurrence.
A kind of character, or a shape: REGEXREPLACE.

Remember that in a pattern some characters are special. To replace a real dot, write \.. With SUBSTITUTE, a dot is just a dot.

Extracting every match

REGEXEXTRACT returns only the first match. To gather all of them, replace everything that is not wanted with a separator and split:

=SPLIT(REGEXREPLACE(A2, "\D+", " "), " ")

That returns every number in the cell, each in its own cell.

Common mistakes

Special characters not escaped: . + * ? ( ) [ ] $ ^ | and the backslash itself.
Expecting only the first match to change.
A greedy .* removing more than intended. .*? stops sooner.
Forgetting case sensitivity. Start the pattern with (?i) to ignore case.
Treating the result as a number.

In Excel

Excel 2021 and earlier have no REGEXREPLACE. Recent Microsoft 365 has added one. In other versions, nested SUBSTITUTE formulas do the simple cases.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.