Data analysts and spreadsheet power users spend an enormous proportion of their working hours cleaning imported datasets—stripping punctuation from phone numbers, standardising postcode formats, extracting serial codes, and verifying email syntax. Traditionally, accomplishing these tasks in Microsoft Excel required nesting dozens of complicated MID, FIND, SUBSTITUTE, and LEFT functions, or writing custom VBA macros. To modernise data manipulation, Microsoft added native Regular Expression (Regex) functions to Microsoft Excel.
Among these new dynamic array tools, REGEXTEST and REGEXREPLACE provide unmatched power for auditing and sanitising data. Here is a comprehensive guide on their syntax, pattern rules, and practical business applications.
Understanding the Core Regex Functions
The two functions address complementary data engineering tasks:
- REGEXTEST: Evaluates whether a cell’s text matches a specified regex pattern, returning a simple
TRUEorFALSE. It is ideal for data validation, error flagging, and conditional formatting rules. - REGEXREPLACE: Finds all substrings matching a regex pattern and replaces them with a specified replacement string, making it effortless to remove symbols, reformat phone numbers, or redact private information.
Function Syntax and Arguments
REGEXTEST Syntax
=REGEXTEST(text, pattern, [case_sensitivity])text: The cell or text string to evaluate.pattern: The regular expression string (e.g.,"^[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,}$").case_sensitivity: Optional.0for case-insensitive (default),1for case-sensitive.
REGEXREPLACE Syntax
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])text: The cell containing raw data.pattern: The regex pattern to match.replacement: The string to substitute into matching portions.occurrence: Optional.0to replace all occurrences (default), or an integer specifying a particular occurrence.
Practical Example 1: Validating Email Addresses with REGEXTEST
To confirm whether a column of user registrations contains valid email formats:
=REGEXTEST(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")If cell A2 contains [email protected], the formula returns TRUE. If the cell contains an invalid entry like john.doe@missingdomain, it returns FALSE. You can easily wrap this in an IF function to flag invalid records:
=IF(REGEXTEST(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"), "Valid", "Action Required")Practical Example 2: Stripping Non-Numeric Characters with REGEXREPLACE
Phone numbers imported from web forms frequently arrive with erratic formatting: +44 (0)20 7946 0912, 020-7946-0912, or (020) 7946 0912. To strip away all non-numeric characters in a single calculation:
=REGEXREPLACE(A2, "[^0-9]", "")The pattern [^0-9] targets any character that is not a digit between 0 and 9 and replaces it with an empty string "", returning clean digits: 4402079460912.
| Pattern | What It Matches | Common Application |
|---|---|---|
[0-9]+ |
One or more consecutive digits | Extracting invoice or order numbers |
\s+ |
Consecutive whitespace or tabs | Normalising erratic spacing between words |
[^a-zA-Z0-9 ] |
All special punctuation symbols | Cleaning customer names and database keys |
^\d{5}(-\d{4})?$ |
Standard 5-digit or 9-digit postal codes | Validating shipping addresses |
Practical Example 3: Reformatting Dates or Order Codes
Regex capture groups allow you to rearrange token positions. For example, to convert dates formatted as DD/MM/YYYY to ISO YYYY-MM-DD format:
=REGEXREPLACE(A2, "(\d{2})/(\d{2})/(\d{4})", "$3-$2-$1")The capture groups $1, $2, and $3 reference the corresponding parenthesis expressions, rearranging the tokens into the desired order instantly.