Excel Function: REGEXREPLACE

The REGEXREPLACE function replaces one or more parts of a text based on a regular expression.

Usage:

=REGEXREPLACE(text, regex, replacement)

or:

=REGEXREPLACE(text, regex, replacement, occurrence, case)

If you do not know what a regular expression is or how to write one, first start with the full tutorial that accompanies the REGEXTEST function before continuing.

Simple replacement

The REGEXREPLACE function here replaces the word "fun" with the word "enjoyable":

=REGEXREPLACE(A2,"fun","enjoyable")

excel function regexreplace word

The problem with this formula is that if the text contains the word "funny" (with "ny"), it will be replaced by "enjoyableny" (as you can see in the colored cell).

To avoid this problem, you must specify that "fun" may or may not be followed by "ny":

=REGEXREPLACE(A2,"fun(ny)?","enjoyable")

excel function regexreplace words

Removing characters

To remove all non-numeric characters (and keep only the phone number here), enter the regex "\D" and replace these characters with an empty string "":

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

excel function regexreplace phone numbers

Extract domains

The following formula removes all characters up to the "@" to get only the domain names from email addresses:

=REGEXREPLACE(A2,".*@","")

excel function regexreplace extract email domain

To extract only the domain name without the extension, the formula then becomes:

=REGEXREPLACE(A2,".*@|\.[a-z]+","")

excel function regexreplace extract domain

Change date formats

The following formula converts dates from the EU format (day/month/year) to the US format (year/month/day) :

=REGEXREPLACE(A2,"([0-3]?\d)\W([0-1]?\d)\W(20\d{2})","$3/$2/$1")

$1 returns the value of the first pair of parentheses, $2 returns the value of the second pair of parentheses, etc.

To validate this regex, the date (which is here in text format) must:

([0-3]?\d): start with 1 or 2 digits (parentheses = $1 in the replacement value)
\W: be followed by a non-alphanumeric character
([0-1]?\d): be followed by 1 or 2 digits (parentheses = $2 in the replacement value)
\W: be followed by a non-alphanumeric character
(20\d{2}): be followed by a number between 2000 and 2099 (parentheses = $3 in the replacement value)

excel function regexreplace date format

Other arguments

You can specify the occurrence number to replace as the third argument (default 0, all occurrences). To count from the end, enter a negative value.

To ignore case, enter the value 1 as the fourth argument of the function (default 0, case sensitive).

For example, the following formula replaces only the second occurrence and ignores case:

=REGEXREPLACE(A2,"spreadsheets?","EXCEL",2,1)

excel function regexreplace occurrence case