The REGEXREPLACE function allows you to replace text from a string with another string, based on a supplied regular expression ("regex").
Syntax
The REGEXREPLACE function replaces strings within the provided text that matches the pattern with replacement.
The syntax of the REGEXEXTRACT function is:
REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
|
Argument |
Description |
|---|---|
|
text (required) |
The text or the reference to a cell containing the text you want to replace strings within. |
|
pattern (required) |
The regular expression ("regex") that describes the pattern of text you want to replace. |
|
replacement (required) |
The text you want to replace instances of pattern. |
|
occurrence |
Specifies which instance of the pattern you want to replace. By default, occurrence is 0, which replaces all instances. A negative number replaces that instance, searching from the end. |
|
case_sensitivity |
Determines whether the match is case-sensitive. By default, the match is case-sensitive. Enter one of the following: 0: Case sensitive 1: Case insensitive |
Notes: When writing regex patterns, symbols called ‘tokens’ can be used that match with a variety of characters. These are some simple tokens for reference:
-
“[0-9]”: any numerical digit
-
“[a-z]”: a character in the range of a to z
-
“.”: any character
-
“a”: the “a” character
-
“a*”: zero or more “a”
-
“a+”: one or more “a”
-
All regular expressions for this function, as well as REGEXTEST and REGEXEXTRACT use the PCRE2 'flavor' of regex.
-
REGEXEXTRACT always return text values. You can convert these results back to a number with the VALUE function.
Examples
Copy the example data and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Use REGEXREPLACE to anonymize phone numbers by replacing their first 3 digits with ***, using pattern
"[0-9]+-"
|
Data |
|
Sonia Rees(378) 555-4195 Angel Brown(878) 555-8622 Blake Martin(437) 555-8987 William Kirby(619) 555-4212 Avery Smith(579) 555-5658 Parker Jones(346) 555-0925 Liam Small(405) 555-0887 Hollie Rees(666) 555-1872 |
|
Formulas |
|
=REGEXREPLACE(A2,"[0-9]+-","***-") |
The following illustration shows the result. You will need to wrap text on cell A4 to show the line breaks in the result.
![Using REGEXREPLACE to replace the first three digits of each phone number in mixed text with *, using pattern "[0-9]+-"](https://support.content.office.net/en-us/media/bc730dc8-724c-4670-ac0b-936b74d9f7bb.png)
Use REGEXREPLACE with capturing groups to separate and reorder given name and last name, using pattern: "([A-Z][a-z]+)([A-Z][a-z]+)"; and replacement: "$2, $1".
Note: Capturing groups are defined in pattern with parentheses "()", and can be referenced in replacement as "$n". In this example, $1 and $2 reference the first and second capturing groups, respectively.
|
Data |
|
SoniaBrown |
|
Formulas |
|
=REGEXREPLACE(A2,"([A-Z][a-z]+)([A-Z][a-z]+)","$2, $1") |
The following illustration shows the results.
![Use REGEXREPLACE with capturing groups to separate and reorder given name and last name, using pattern: "([A-Z][a-z]+)([A-Z][a-z]+)" and replacement: "$2, $1"](https://support.content.office.net/en-us/media/c04ce32a-004a-4878-ae47-14596f5ed949.png)
