REGEXREPLACE function
Quick answer
REGEXREPLACE replaces the parts of a text that match a regular expression.
- Syntax
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])- Most common use
=REGEXREPLACE(A2,"\D","")returns 5550142299 (Every non-digit removed)
Use it to strip everything but digits from phone numbers, collapse repeated spaces, or reorder names with captured groups: $1 and $2 put back what each pair of parentheses matched. occurrence picks a single match to replace instead of all of them.
- Available in
- Microsoft 365 only
- Category
- Text functions
- Returns
- The text with the matches replaced.
- Google Sheets
- Google Sheets has REGEXREPLACE with three arguments: it always replaces every match.
Syntax
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text to change. |
pattern | Required | The regular expression to find. |
replacement | Required | The new text. $1, $2… insert captured groups. |
occurrence | Optional | Optional: 0 (default) replaces every match; 1 the first, 2 the second; negative numbers count from the end. |
case_sensitivity | Optional | Optional: 0 (default) for case-sensitive, 1 to ignore case. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B2 | =REGEXREPLACE(A2,"\D","") | 5550142299 | Every non-digit removed |
| B3 | =REGEXREPLACE(A3,"(\w+), (\w+)","$2 $1") | Ana Lopez | The two captured names swapped |
| B4 | =REGEXREPLACE(A4," +"," ") | too many spaces | Runs of spaces become one space |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Raw | Result |
| (555) 014-2299 | 5550142299 |
| Lopez, Ana | Ana Lopez |
| too many spaces | too many spaces |
Practice REGEXREPLACE
Graded instantly, then re-checked on changed data.
Digits only
A2:A6 were typed in different styles. In B2, remove every character that isn't a digit. Fill down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Nothing changes | The pattern didn't match. Check the case (it's case-sensitive by default), and escape special characters such as . ( ) with a backslash. |
| $1 shows up literally | The pattern has no capture group. Put the part you want to keep in parentheses. |
| A number becomes text | The result is always text. Wrap it in VALUE to calculate with it. |
Tips
- =REGEXREPLACE(A2,"\s+"," ") turns runs of spaces, tabs and line breaks into single spaces.
Questions
How do I remove all non-numeric characters in Excel?
=REGEXREPLACE(A2,"\D","") keeps only the digits. Wrap it in VALUE for a number.
How do I replace only the first match?
Set occurrence to 1: =REGEXREPLACE(A2,"-","/",1).
Answers that use REGEXREPLACE
Updated Checked by running: every formula on this page was run by our formula engine on .