REGEXREPLACE function in Google Sheets
REGEXREPLACE replaces every part of the text that matches a regular expression. It strips unwanted characters from phone numbers, collapses extra spaces and reorders pieces of text, using $1 and $2 to put captured groups back into the result.
- Works in
- Google Sheets only
- Returns
- Text with every match replaced.
Syntax
=REGEXREPLACE(text, regular_expression, replacement)
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text to change. It has to be text: wrap numbers in TO_TEXT. |
regular_expression | Required | The pattern to find. Every match is replaced, not just the first. |
replacement | Required | What to put in place of each match. $1 inserts the first captured group, $2 the second. Use "" to delete the matches. |
Examples
Each result is calculated from the sample sheet by our formula engine in Sheets mode, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B2 | =REGEXREPLACE(A2,"\D","") | 6125550148 | \D means any character that isn't a digit, so only the digits are left |
| B3 | =REGEXREPLACE(A3,"(\w+), (\w+)","$2 $1") | Ana Lima | Two capture groups swapped: Lima, Ana becomes Ana Lima |
| B4 | =REGEXREPLACE(A4,"\s+"," ") | Too many spaces | Any run of spaces becomes a single space |
Try it live
This is the sample sheet. Change any value or edit a formula, and the results update.
Practice REGEXREPLACE
Graded instantly in Sheets mode, then re-checked on changed data.
Clean phone numbers
B2, remove every character that isn't a digit from the phone number in A2, so each one becomes ten digits. Keep the result as text. Fill down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The result is text, not a number | Stripping a phone number or price down to digits still gives text. Wrap it in VALUE if you need a number. |
| More was replaced than you expected | REGEXREPLACE changes every match, not only the first. Anchor the pattern with ^ or $ to change only the start or the end. |
| Dots, brackets or dollar signs behave strangely | Characters like . ( ) $ and + have special meanings in a pattern. Escape them with a backslash: "\." matches a real dot and "\$" a dollar sign. |
| #VALUE! on a number | REGEXREPLACE needs text. Convert first: =REGEXREPLACE(TO_TEXT(A2),"0",""). |
Tips
- Test a pattern with REGEXMATCH first, then switch to REGEXREPLACE once it matches what you expect.
- Remove several different words at once with |: =REGEXREPLACE(A2,"(?i)inc\.?|ltd\.?","").
REGEXREPLACE in Excel
Recent Microsoft 365 builds include REGEXREPLACE as well. In other versions, SUBSTITUTE replaces fixed text, but there is no pattern-based replace.
Questions
How do I remove all non-numeric characters in Google Sheets?
Use =REGEXREPLACE(A2,"\D",""). Only the digits are left. Wrap it in VALUE if you need a number.
How do I swap first and last names?
Capture both parts and reorder them: =REGEXREPLACE(A2,"(\w+), (\w+)","$2 $1") turns "Lima, Ana" into "Ana Lima".
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine in Sheets mode.