INDEX
About the REGEXREPLACE Function
Overview of REGEXREPLACE
Text Replacement Using Regular ExpressionsGoogle Sheets Function | ||
=REGEXREPLACE( text, regex, replacement ) Summary The REGEXREPLACE function replaces text that matches a regular expression with a specified replacement string. |
||
|
When to Use REGEXREPLACE
- When you need to replace parts of a string based on a specific pattern.
- When reformatting data to ensure uniformity.
- When removing unnecessary text or characters.
How to Use REGEXREPLACE
The following table demonstrates the basic usage of the REGEXREPLACE function.
A | B | |
---|---|---|
1 | Text | Replacement Result |
2 | 123-456-7890 | =REGEXREPLACE(A2, “-“, ” “) |
3 | abc123xyz | =REGEXREPLACE(A3, “[0-9]+”, “###”) |
Results
- In cell B2, the hyphen
-
is replaced with a space, resulting in 123 456 7890. - In cell B3, the numbers
[0-9]+
are replaced with ###.
Regular Expression Reference

Google Sheets Regular Expressions: Examples with REGEXREPLACE Function
By using the REGEXREPLACE function in Google Sheets, you can easily perform text replacement based on regular expressions. This article explains nearly all comm...
Advanced Examples
Here are some advanced use cases for REGEXREPLACE:
Example 1: Reformat Phone Numbers
Reformat phone numbers into the format “(123) 456-7890”.
A | B | |
---|---|---|
1 | Original Number | Formatted Number |
2 | 1234567890 | =REGEXREPLACE(A2, “([0-9]{3})([0-9]{3})([0-9]{4})”, “($1) $2-$3”) |
- In this example, 1234567890 is reformatted as (123) 456-7890.
Example 2: Replace Multiple Spaces with a Single Space
Reduce multiple spaces in text to a single space.
A | B | |
---|---|---|
1 | Text | Formatted Text |
2 | This is sample text. | =REGEXREPLACE(A2, “\\s+”, ” “) |
- In this example, multiple spaces are condensed into a single space.
Points to Note
- Errors may occur if there are mistakes in the regular expression.
- You can use special symbols (e.g.,
$1
,$2
) in the replacement string to reference groups in the regular expression. - With practice, you can perform complex replacements using advanced regular expressions.
Conclusion
- The REGEXREPLACE function enables flexible text replacement using regular expressions.
- It is ideal for reformatting data or removing unnecessary text.
- Combining it with other functions allows for more advanced data processing.