Replacing Patterns With REGEXREPLACE

So far you tested patterns and pulled pieces out. Now you will rewrite text: stripping junk characters, reformatting phone numbers, or collapsing extra spaces.

REGEXREPLACE finds every part of the text that matches a pattern and swaps it for replacement text you choose.

This Google Sheets function is the heavy lifter of text cleaning, because it can transform a whole messy column in one pass.

REGEXREPLACE(text, pattern, replacement) takes three arguments: the source text, the pattern to find, and the replacement string.

Unlike REGEXEXTRACT, it replaces every match it finds, not just the first.

The formula below removes all digits from A2 by replacing each digit with an empty string. Room 204 becomes Room .

A common trick is to delete characters by replacing them with an empty string "".

The formula below strips out anything that is not a digit, leaving only the numbers. (555) 123-4567 becomes 5551234567. The [^\d] class means any character that is not a digit, and + grabs runs of them at once.

Imported text often has double or triple spaces. You want to squeeze any run of spaces down to a single space.

The formula below matches one or more whitespace characters and replaces each run with a single space. Combine it with TRIM later to clean the ends too.

The replacement text can refer back to captured groups using $1, $2, and so on. $1 means whatever the first capture group matched.

This lets you reorder or reformat. The formula below turns Smith, John into John Smith by capturing the two names and swapping their order.

Suppose A2 holds a clean 10-digit string like 5551234567 and you want (555) 123-4567.

Capture three digits, three digits, and four digits, then rebuild with formatting in the replacement. Each group is referenced by its number.

You can replace literal words too. To standardize abbreviations, match the word and swap it.

The formula below changes every St. into Street in an address. Remember to escape the dot as \. so it matches a literal period rather than any character.

Regex is case sensitive by default. To match a word regardless of case, list the variants in a class or build alternatives.

The formula below replaces color or Color with colour. The [Cc] matches either case of the first letter.

To hide all but the last four digits of a card number, replace the leading digits with X.

The formula below matches all digits that come before the final four and replaces each with an X. For 1234567890123456 you get XXXXXXXXXXXX3456.

Real cleaning often needs several steps. You can nest REGEXREPLACE calls, with the inner result feeding the outer.

The formula below first removes anything that is not a letter or space, then collapses multiple spaces into one. Reading inside out shows the order of operations.

Like the other regex functions, REGEXREPLACE is meant to clean an entire column in one pass. You write the formula once and fill it down beside your raw data.

Suppose column A holds product names cluttered with trailing codes like Chair (SKU-4821). The formula below removes the parenthetical code from every row, leaving just Chair . Add a TRIM around it to drop the leftover space.

Test your grasp of pattern replacement.

You learned that REGEXREPLACE(text, pattern, replacement) rewrites every match in Google Sheets. Key points:

• Replace with "" to delete unwanted characters
• [^\d] and \s+ are workhorses for cleaning
• Capture groups can be reused in the replacement as $1, $2
• Escape literal specials like \.
• Nest calls to chain multiple cleanups

Next you will pair regex with SPLIT and CLEAN to break apart and finish tidying imported data.