REGEXREPLACE replaces every matching part of text using a regular expression. Use it to remove unwanted characters, normalize whitespace or rearrange captured text.
REGEXREPLACE function syntax
=REGEXREPLACE(text, regular_expression, replacement)- text: text to edit.
- regular_expression: RE2 matching pattern.
- replacement: replacement text; $1 and $2 reuse captured groups.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Text |
|---|
| (202) 555-0143 |
| Two words |
| Smith, Alex |
| SKU-1042 |
| ‘0000111122223333 |
Keep only digits
Enter this formula in A10. The negated class matches every nondigit, replacing it with nothing. The result remains text, which is appropriate for phone numbers and identifiers.
=REGEXREPLACE(A2,"[^0-9]","")Result: 2025550143.
Normalize spaces, tabs and line breaks
The pattern combines runs of ASCII whitespace into one ordinary space. TRIM removes spaces at the edges.
=TRIM(REGEXREPLACE(A3,"\s+"," "))Result: Two words.
Reorder a name around a comma
The first group captures the surname and the second captures the remaining name. The replacement reverses those groups. This assumes exactly one separating comma.
=REGEXREPLACE(A4,"^([^,]+),\s*(.+)$","$2 $1")Result: Alex Smith.
Remove only a leading prefix
The start anchor prevents removing SKU- elsewhere in the text. SUBSTITUTE is simpler when every literal occurrence should be replaced.
=REGEXREPLACE(A5,"^SKU-","")Result: 1042.
Mask a fixed-length example ID
This fictional 16-character ID is stored as text. Twelve leading digits are replaced; the last four remain.
=REGEXREPLACE(A6,"^[0-9]{12}","************")Result: ************3333.
Replace a line break with a comma
CHAR(10) supplies a line break in this reproducible example. The replacement joins the two lines with a comma and a space.
=REGEXREPLACE("First"&CHAR(10)&"Second","\n",", ")Result: First, Second.
Other Google Sheets articles you may also like