Google Sheets Function: REGEXREPLACE

The REGEXREPLACE function replaces one or more parts of a text using regular expressions.

Usage:

=REGEXREPLACE(text, regular_expression, replacement)

If you are not familiar with regular expressions or how to write them, start by reading the complete tutorial accompanying the REGEXMATCH function before continuing (to avoid headaches when reading some regex on this page).

Simple Replacement

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

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

google sheets function regexreplace word

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

To avoid this, you must specify that "fun" can be followed by "ny":

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

google sheets function regexreplace words

Removing Characters

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

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

google sheets function regexreplace phone numbers

Replace Email Addresses

The following formula detects and replaces email addresses (this regex is detailed in one of the examples of the REGEXMATCH function):

=REGEXREPLACE(A2,"[\w.-]+@[\w.-]{2,}\.[a-z]{2,}","[email address removed]")

google sheets function regexreplace replace email

To remove only the first part of the email address and replace it with "xxxxxx", enclose the second part to be kept in parentheses and add it to the replacement address using $1:

=REGEXREPLACE(A2,"[\w.-]+(@[\w.-]{2,}\.[a-z]{2,})","xxxxxx$1")

google sheets function regexreplace mask email

$1 returns the value of the first pair of parentheses, $2 returns the value of the second pair of parentheses, and so on.