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...
programming-cafe.com
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.