How to Use REGEXREPLACE in Google Sheets: Comprehensive Guide

REGEXREPLACE replaces every part of a text that matches a regular expression with new text. It is the strongest cleaning tool in Sheets: strip all non-digits from a phone number, collapse repeated spaces, reformat dates, mask emails, or reorder "Last, First" into "First Last" in a single formula.

Syntax

=REGEXREPLACE(text, regular_expression, replacement)

text: The text to modify.
regular_expression: The pattern to find, as text in quotes. Google Sheets uses the RE2 syntax.
replacement: The text to put in place of each match. It can refer to captured groups as $1, $2, and so on.

All matches are replaced, not just the first. Matching is case-sensitive unless the pattern starts with (?i).

Basic examples

=REGEXREPLACE("(555) 123-4567", "\D", "")

Returns 5551234567: every non-digit (\D) is replaced with nothing.

=REGEXREPLACE("too    many   spaces", "\s+", " ")

Returns too many spaces: any run of whitespace becomes a single space.

=REGEXREPLACE("Hello World", "(?i)world", "Sheets")

Returns Hello Sheets, case-insensitively.

Using captured groups

Parentheses capture part of the match, and $1, $2, ... put those parts back in the replacement. This lets you rearrange text:

=REGEXREPLACE("Doe, Jane", "(\w+), (\w+)", "$2 $1")

Returns Jane Doe: group 1 is the last name, group 2 the first name, and the replacement swaps them.

=REGEXREPLACE("2026-03-15", "(\d{4})-(\d{2})-(\d{2})", "$3/$2/$1")

Returns 15/03/2026: a date reformatted from year-first to day-first.

Regex building blocks

\d digit, \D non-digit
\w letter/digit/underscore, \W the opposite
\s whitespace, \S non-whitespace
. any character, + one or more, * zero or more, ? optional
[aeiou] any listed character, [^a-z] anything except lowercase letters
^ start, $ end
a|b a or b
(?i) ignore case

To match a literal special character such as ., ( or $, put a backslash in front.

Practical patterns

Keep only digits (or only letters)

=REGEXREPLACE(A2, "\D", "")

=REGEXREPLACE(A2, "[^A-Za-z]", "")

Remove extra spaces

=REGEXREPLACE(TRIM(A2), "\s+", " ")

TRIM handles ordinary spaces. The pattern also collapses tabs and line breaks.

Remove non-printable characters and line breaks

=REGEXREPLACE(A2, "[\n\r\t]", " ")

Strip HTML tags

=REGEXREPLACE(A2, "<[^>]+>", "")

Mask part of an email

=REGEXREPLACE(A2, "^(.).*(@.*)$", "$1***$2")

Turns ana@example.com into a***@example.com.

Remove text in parentheses

=REGEXREPLACE(A2, "\s*\([^)]*\)", "")

Normalize a phone number

=REGEXREPLACE(REGEXREPLACE(A2, "\D", ""), "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")

First strip everything but digits, then format a 10-digit number.

Remove everything after a character

=REGEXREPLACE(A2, "\?.*$", "")

Drops a URL's query string, such as ?utm_source=....

Cleaning a whole column

=ARRAYFORMULA(REGEXREPLACE(A2:A, "\D", ""))

Add IF(A2:A = "", "", ...) if blanks should stay blank.

Common errors

#VALUE!: "Invalid regular expression". A syntax problem such as an unclosed bracket or parenthesis, or an unsupported feature (lookaheads and backreferences in the pattern).
Numbers come back as text. The result is always text. Wrap in VALUE to calculate with it.
Leading zeros disappear in the input. Numbers are converted to text first, so 00123 stored as a number is already 123. Format the source column as plain text.
Nothing changes. Case sensitivity (add (?i)), a literal character that needs escaping, or spaces that are non-breaking (CHAR(160)). Handle those with REGEXREPLACE(A2, "\x{00A0}", " ").
Too much replaced. . matches almost anything, and .* is greedy. Use .*? or a more specific class such as [^)]*.

REGEXREPLACE vs. SUBSTITUTE vs. REPLACE

SUBSTITUTE replaces fixed text and is simpler and faster for exact strings.
REPLACE swaps characters by position.
REGEXREPLACE is for patterns: anything with a shape, multiple variants, or rearrangement via captured groups.

Frequently asked questions

Does it replace all matches or just the first? All of them.

Is it case-sensitive? Yes. Start the pattern with (?i) to ignore case.

How do I delete matches? Use an empty string "" as the replacement.

How do I use the matched text in the replacement? Wrap the part in parentheses and use $1, $2, etc. For the whole match, wrap the entire pattern in parentheses and use $1.

Related Functions

REGEXEXTRACT: Extract matching text.
REGEXMATCH: Test whether text matches a pattern.
SUBSTITUTE: Replace specific text without patterns.
TRIM: Remove extra spaces.
SPLIT: Split text at a delimiter.

REGEXREPLACE