How to Use the REGEXREPLACE Function in Google Sheets
=REGEXREPLACE(text, regular_expression, replacement). An empty replacement "" deletes the matches.
What REGEXREPLACE does
SUBSTITUTE swaps one fixed piece of text for another. REGEXREPLACE swaps anything that matches a pattern, so one formula can clean many different values. The pattern can be as simple as a single character.
REGEXREPLACE syntax
=REGEXREPLACE(text, regular_expression, replacement)
text: the text in which part will be replaced.regular_expression: the pattern to match. Per Google's help page, every match in the text is replaced.replacement: the text inserted in place of each match.
The function accepts only text as input and returns text. To get a number back, wrap the formula in VALUE. To pass in a number, convert it with TEXT. Sheets supports RE2, except for Unicode character class matching.
A step-by-step example
Cell A2 holds a phone number with hyphens.
| A | |
|---|---|
| 1 | Contact |
| 2 | Phone: 090-1234-5678 |
=REGEXREPLACE(A2, "-", "")
This returns Phone: 09012345678. Here is why: every hyphen matches the pattern, and each one is replaced with an empty string, so all of them disappear. A pattern can also match words:
=REGEXREPLACE(A2, "Phone", "Tel")
This returns Tel: 090-1234-5678.
Common mistakes
- Passing a number. Convert it to text first.
- Expecting only the first match to change. Every match is replaced.
- Forgetting that characters like a dot are special in patterns. Put a backslash in front to match a literal dot.
- Using the result as a number. It is text until you wrap it in VALUE.
Try it yourself
Use the cell above (A2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Remove the hyphens
Write a formula that removes every hyphen from the text in A2.
Exercise 2: Swap a word
Write a formula that replaces the word Phone in A2 with Tel.
What to learn next
- SUBSTITUTE: replace text inside a string.
- REGEXMATCH: test text against a pattern.
- REGEXEXTRACT: pull text out with a pattern.
Syntax and rules in this guide were checked against Google's REGEXREPLACE help page. See all guides.