If you want to find a pattern inside a string and swap it for something else, the REGEXREPLACE function in Google Sheets does exactly that. You give it the text, a regular expression, and what to put in place of every match.
In this article, I’ll show you how to use REGEXREPLACE to clean phone numbers and collapse whitespace. I’ll also cover reordering names with capture groups, stripping a fixed prefix, and masking credit card digits.
REGEXREPLACE Function Syntax in Google Sheets
The REGEXREPLACE function replaces every part of a text string that matches a regular expression with a replacement string.
=REGEXREPLACE(text, regular_expression, replacement)
- text is the string to search in. Usually a cell reference like A2.
- regular_expression is the RE2 pattern to look for, wrapped in double quotes.
- replacement is what each match gets replaced with. Can include backreferences like $1 and $2 to reuse captured groups.
Google Sheets uses the RE2 regex engine. It does not support lookahead, lookbehind, or backreferences inside the pattern itself. Backreferences in the replacement string work fine.
When to Use REGEXREPLACE Function
- Strip out unwanted characters from a string, like punctuation or non-digits.
- Normalize messy formatting, like extra spaces or inconsistent separators.
- Reorder parts of a string by capturing groups and rearranging them in the replacement.
- Remove a fixed prefix or suffix from a column of codes or labels.
- Mask sensitive data, like card numbers or account IDs, while keeping part of the original visible.
Example 1: Strip Every Non-Digit From a Phone Number
Let’s start with one of the most useful cleanup jobs, pulling just the digits out of messy phone numbers.
Below is the dataset. Column A has phone numbers written in all kinds of formats, with parentheses, dashes, dots, and spaces mixed in.
I want the digits only, with everything else stripped out.
Here is the formula:
=REGEXREPLACE(A2, "[^0-9]", "")
The pattern [^0-9] is a negated character class. The ^ inside the brackets means “anything that is NOT in this set”, so it matches every character that is not a digit. Those characters get replaced with an empty string, which is just nothing.
The first row turns into 4155552671, the second into 12025550143, and the rest follow suit. Only the digits survive.
Pro Tip: An empty string as the replacement is how you delete things with REGEXREPLACE. Pass “” and every match disappears from the result.
Example 2: Collapse Runs of Whitespace Into One Space
Sometimes text comes in with double or triple spaces sprinkled through it, usually after a copy-paste from somewhere else.
Below is the dataset. Column A has sentences with random extra spaces between words.
I want every run of whitespace squeezed down to a single space.
Here is the formula:
=REGEXREPLACE(A2, "\s+", " ")
The pattern \s+ matches one or more whitespace characters in a row. The \s is the shorthand for any whitespace (space, tab, newline) and the + means one or more. The replacement is a single space.
So The quick brown fox. becomes The quick brown fox., and every other row gets the same clean-up. Each cluster of spaces, no matter how long, collapses into one.
This pairs well with TRIM if you also want to drop leading and trailing whitespace. REGEXREPLACE handles the middle. TRIM handles the edges.
Example 3: Flip Last, First Into First Last Using Backreferences
This is where REGEXREPLACE gets really useful. You can capture parts of the match and reorder them in the replacement.
Below is the dataset. Column A has names written in “Last, First” format, with the surname first.
I want each name flipped to “First Last” order.
Here is the formula:
=REGEXREPLACE(A2, "(\w+),\s*(\w+)", "$2 $1")
The pattern has two capture groups. The first (\w+) grabs the last name (one or more word characters), then ,\s* skips the comma and any spaces, and the second (\w+) grabs the first name. The replacement "$2 $1" writes the second group, a space, and then the first group.
So Smith, Alice becomes Alice Smith. Patel, Rohan becomes Rohan Patel. The order flips because the replacement string puts $2 (the captured first name) before $1 (the captured last name).
Pro Tip: Capture groups are numbered left to right in the pattern, starting at $1. You can use up to $9 in the replacement string for more complex reorderings.
Example 4: Strip a Fixed Prefix Off Product Codes
When every value in a column starts with the same prefix and you want it gone, anchor the pattern so only the leading match gets removed.
Below is the dataset. Column A has product codes that all start with SKU-.
I want the SKU- prefix stripped off, leaving just the numeric part.
Here is the formula:
=REGEXREPLACE(A2, "^SKU-", "")
The ^ at the start of the pattern anchors the match to the beginning of the string. So SKU- only matches when it sits at the very start, not anywhere in the middle. The replacement is an empty string, so the matched prefix disappears.
SKU-1042 becomes 1042, SKU-2218 becomes 2218, and so on down the column. The anchor matters here. Without the ^, the pattern would happily strip a SKU- from the middle of a string too.
Example 5: Mask All But the Last Four Card Digits
A common privacy pattern is showing only the last four digits of a card number and replacing the rest with asterisks.
Below is the dataset. Column A has 16-digit card numbers.
I want the first twelve digits masked, leaving the last four readable.
Here is the formula:
=REGEXREPLACE(A2, "^\d{12}", "************")
The pattern ^\d{12} matches exactly twelve digits anchored at the start of the string. \d is shorthand for any digit, and {12} is a quantifier that says “exactly twelve of these”. The replacement is twelve asterisks, one for each masked digit.
So 4532123456781234 becomes ************1234, and every other row gets the same treatment. The last four digits stay visible because the pattern only matches the leading twelve.
Pro Tip: RE2 does not support lookahead, so the typical `\d(?=\d{4})` trick from other regex flavors won’t work here. The fixed-quantifier approach with `{12}` is the clean Google Sheets pattern when you know the string length.
Tips & Common Mistakes
- RE2 has no lookahead, lookbehind, or pattern backreferences. Google Sheets uses Google’s RE2 engine, which leaves out a few features common to other regex flavors. Patterns like
(?=...)for lookahead or\1inside the pattern itself will throw an error. Replacement backreferences like$1work fine. - For literal text swaps, use SUBSTITUTE. If you don’t need a pattern at all and just want to replace one exact string with another, the SUBSTITUTE function is simpler and faster. Use REGEXREPLACE only when the match really needs a pattern.
- Quote the pattern, and watch your character classes. The regular expression is a string, so it lives inside double quotes. A common slip is forgetting that
^inside[...]means negation, while^outside the brackets anchors to the start of the string. Two different meanings, same character.
REGEXREPLACE is the pattern-based sibling of SUBSTITUTE. SUBSTITUTE handles exact text swaps. REGEXREPLACE handles anything you can describe as a regular expression.
You’ve now seen it stripping non-digits, collapsing whitespace, reordering names with capture groups, removing a fixed prefix, and masking sensitive digits. Same function, five different cleanup jobs.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: