1. Introduction to the REGEXREPLACE Function
The REGEXREPLACE function is a powerful tool in Excel that allows users to find and replace substrings within a text using regular expressions. This is particularly useful for cleaning up data or reformatting strings in bulk.
2. Parameters of the REGEXREPLACE Function
Here is the syntax for using the
REGEXREPLACE function.=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])text: required The text or the reference to a cell containing the text you want to replace strings within. pattern: required The regular expression ("regex") that describes the pattern of text you want to replace. replacement: required The text you want to replace instances of pattern. occurrence: optional Specifies which instance of the pattern you want to replace. By default, occurrence is 0, which replaces all instances. A negative number replaces that instance, searching from the end. case_sensitivity: optional Determines whether the match is case-sensitive. By default, the match is case-sensitive. Enter one of the following: 0: Case sensitive1: Case insensitive3. Example Scenario: Phone Number Formatting
For instance, suppose you have a list of phone numbers in various formats and you want to standardize them to the format
(xxx) xxx-xxxx. Using the REGEXREPLACE function can simplify this process drastically.4. Getting Started with the REGEXREPLACE Function
Type on the keyboard:
=REGEXREPLACE(, then select A5 as the text to process.
5. Remove Non-Numeric Characters
Here we set the regular expression to:
"\D", which matches any non-numeric character, and then set the replacement to an empty string: "".
6. Apply the REGEXREPLACE Function
This way, we can replace all non-numeric characters in the phone numbers with nothing.
You may see that the data appears unchanged because the original format is already numeric. Let's apply the same processing to other rows.
You may see that the data appears unchanged because the original format is already numeric. Let's apply the same processing to other rows.
7. Successfully Filtered Out Non-Numeric Characters
Now, we have successfully filtered out the non-numeric characters from the phone numbers.
8. Format Phone Numbers
We can also convert the phone numbers into a fixed format of
The regular expression used here is:
(xxx) xxx-xxxx. The regular expression used here is:
"(\d{3})[.-]?\s?(\d{3})[.-]?\s?(\d{4})", and the replacement regex is: "($1) $2-$3".
9. Regular Expression Match Parsing
Here we use three regular expression capture groups to divide the phone number into three parts. Each part matches the digits in the phone number.
Text:
Regular Expression:
123-456-7890(\d{3})[.-]?\s?(\d{3})[.-]?\s?(\d{4})1
123(\d{3})2
-[.-]?\s?3
456(\d{3})4
-[.-]?\s?5
7890(\d{4})1
(\d{3}) ( \d Digit{3} Quantifier) 2
[.-]?\s? [.-] Character set., character -.? Quantifier\s Character set? Quantifier3
(\d{3}) ( \d Digit{3} Quantifier) 4
[.-]?\s? [.-] Character set., character -.? Quantifier\s Character set? Quantifier5
(\d{4}) ( \d Digit{4} Quantifier) 10. Regular Expression Replacement Parsing
Then we concatenate the three parts matched by the regex into the desired format.
Text:
Regular Expression:
(123) 456-6789($1) $2-$31
((2
123$13
))4
5
456$26
--7
6789$31
( Character2
$1 Content of the first capture group3
) Character4
whitespace character Character5
$2 Content of the second capture group6
- Character7
$3 Content of the third capture group11. Successfully Formatted Phone Numbers
Thus we have successfully formatted the phone numbers into our desired format. Apply this to other rows to complete the entire data cleaning process.
12. Cases of Regular Expression Match Failure
You might be puzzled that our regular expression
In fact, the reason is that the
"(\d{3})[.-]?\s?(\d{3})[.-]?\s?(\d{4})" did not match the phone numbers originally in the format of (xxx) xxx-xxxx. In fact, the reason is that the
REGEXREPLACE function returns the original text unchanged if the regex does not match. Therefore, there is no need to match phone numbers originally in the format of (xxx) xxx-xxxx.13. 🎉 Finish! 🎉
Author's Note: I hope you can feel the effort I put into these tutorials. I hope to create a series of very easy-to-understand Excel tutorials.If it is useful, help me share these tutorials, thank you!
Follow me:
Related Tutorials