For basic text cleanup, Google Sheets provides the SUBSTITUTE and REPLACE functions. However, these tools fail when you need to clean data based on complex patterns rather than exact matches.
For example, if you have a column of messy data and you need to strip out every single number (leaving only letters), you cannot use SUBSTITUTE because you would have to write 10 nested formulas to remove 0 through 9.
This is where REGEXREPLACE shines. It uses Regular Expressions (Regex)—a universal syntax for pattern matching—to identify complex structures and replace them instantly. In this guide, you will learn how to use the REGEXREPLACE function for advanced data sanitization.
The REGEXREPLACE Syntax
The function requires three arguments:
=REGEXREPLACE(text, regular_expression, replacement)- text: The cell containing the messy string.
- regular_expression: The pattern you are looking for (must be in quotation marks).
- replacement: The text you want to insert in place of the pattern. Use
""(empty quotes) to simply delete the matched pattern.
Use Case 1: Stripping All Numbers from Text
Imagine cell A2 contains the text: John123Smith456. You want to extract just the name.
In Regex, the code for “any digit” is \d (or you can use a character class like [0-9]).
=REGEXREPLACE(A2, "\d", "")Google Sheets scans the cell, identifies every single number, and replaces it with nothing. The output is perfectly clean: JohnSmith.
Use Case 2: Stripping All Non-Numbers (Keeping Only Digits)
Conversely, suppose you have a column of poorly formatted phone numbers: (555) 867-5309 ext: 12. You only want the raw digits.
In Regex, capitalizing the shortcut reverses its meaning. While \d means “digits”, \D means “anything that is NOT a digit” (letters, spaces, punctuation).
=REGEXREPLACE(A2, "\D", "")This formula acts as a vacuum, sucking out all the parentheses, hyphens, spaces, and letters, leaving you with exactly what you need: 555867530912.
Use Case 3: Removing Extra Whitespace
Sometimes imported data contains massive gaps, like this: First Last. The standard TRIM function only removes spaces at the very beginning or end of a string; it will not fix massive gaps in the middle.
In Regex, \s represents a space. Adding a plus sign (+) means “one or more consecutive spaces”.
=REGEXREPLACE(A2, "\s+", " ")This looks for any block of consecutive spaces and replaces the entire block with exactly one single space, resulting in First Last.
A Warning About Special Characters
Regex uses specific punctuation marks as operators (like ., *, +, ?, [, (, $, ^). If you want to use REGEXREPLACE to literally search for a question mark, you cannot just type "?", because Regex interprets that as a command.
You must “escape” the character by placing a backslash in front of it: "\?".
By learning the basics of Regular Expressions and leveraging the REGEXREPLACE function, you can solve text manipulation problems in a single formula that would otherwise require hours of manual auditing.