Introduction to Text Cleaning with REGEXTEST, REGEXEXTRACT, and REGEXREPLACE
A vendor export contained phone numbers in various formats, customer names in last-first order, and order IDs with inconsistent house formats. This article explores how to use the new REGEXTEST, REGEXEXTRACT, and REGEXREPLACE functions to simplify and automate text cleaning tasks in Excel.
REGEXTEST validates a column in one formula, catching invalid entries before they propagate. It uses a regular expression (regex) to describe the text's structure rather than specifying exact positions. This function is particularly useful for ensuring data consistency.
Syntax and Usage
The syntax for REGEXTEST is as follows:
=REGEXTEST(text, pattern, [case_sensitivity])
- text: The cell or range to be checked. - pattern: The regex describing a valid entry. - case_sensitivity: Optional, defaults to 0 (case-sensitive), can be set to 1 (case-insensitive).
For example, to validate order IDs that should be three uppercase letters followed by a hyphen and four digits, use:
This formula will return TRUE if the order ID matches the specified pattern, and FALSE otherwise.
REGEXEXTRACT: Extracting Values Without Counting Characters
REGEXEXTRACT pulls out values from a string without counting characters, making it ideal for extracting values from text where the delimiter is inconsistent. It uses a regex to describe the part of the string to extract.
The syntax for REGEXEXTRACT is as follows:
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
- text: The string to be processed. - pattern: The regex describing the part of the string to extract. - return_mode: Optional, can be 0 (first match), 1 (all matches), or 2 (capture groups within the first match).
For example, to extract the four-digit order number from a product code like ELEC-NORTH-4471-A, use:
This formula will return the four-digit order number, regardless of the prefix length.
REGEXREPLACE replaces text based on a regex pattern, simplifying complex nested formulas. It is useful for replacing values within a text string, such as replacing phone numbers with country codes.
The syntax for REGEXREPLACE is as follows:
- pattern: The regex pattern to match. - replacement: The text to replace the matched pattern with.
This formula will replace the phone number 212 555 1234 with the country code +1 (212) 555-1234.
Conclusion
The REGEXTEST, REGEXEXTRACT, and REGEXREPLACE functions provide a powerful toolkit for automating text cleaning tasks in Excel. These functions are particularly useful for handling inconsistent data formats and reducing the complexity of nested formulas. By leveraging these functions, users can streamline their data processing workflows and improve the accuracy of their Excel models.

Comments