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 |
Note
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.
Example 1
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]+-](media/regexreplace-example-1.png)
Example 2
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](media/regexreplace-example-2.png)