How to Use Regular Expression Functions in Microsoft Excel

Text parsing inside spreadsheet workbooks historically required constructing unwieldy, nested combinations of LEFT, RIGHT, MID, FIND, SEARCH, and SUBSTITUTE functions. Extracting a five-digit postal code or validating an email address from unstructured customer survey notes often required multiple helper columns or complex Visual Basic for Applications (VBA) macro modules.

In modern Microsoft 365 versions of Microsoft Excel, Microsoft introduced native support for Regular Expressions through three high-performance functions: REGEXTEST, REGEXEXTRACT, and REGEXREPLACE. These functions evaluate standardized regular expression syntax directly within cell formulas, providing data analysts with pattern-matching capabilities without requiring macros or external add-ins.

Syntax and Behaviour of the Excel Regex Family

The three native regex functions share consistent argument patterns:

• REGEXTEST(text, pattern, [case_sensitivity]): Evaluates whether the target string satisfies the regular expression pattern. Returns a boolean TRUE or FALSE.
• REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]): Extracts substring occurrences matching the pattern. return_mode can return the first match (0), all matches spilled into cells (1), or capture groups (2).
• REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]): Finds substrings matching the regex pattern and replaces them with a specified replacement string.

Validating Data Formats Using REGEXTEST

You can use REGEXTEST within data validation rules or logical formulas to verify whether entries conform to mandatory structures:

1. To check whether an employee identification code in cell A2 follows the format of two uppercase letters followed by four digits (e.g., AB1234):

=REGEXTEST(A2, "^[A-Z]{2}[0-9]{4}$")

2. To validate whether cell B2 contains a valid corporate email format:

=REGEXTEST(B2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$")

You can nest REGEXTEST inside an IF statement to flag non-compliant records for data cleaning teams:

=IF(REGEXTEST(A2, "^[0-9]{5}$"), "Valid ZIP", "Invalid Entry")

Isolating Specific Substrings with REGEXEXTRACT

When dealing with heterogeneous strings containing mixed text, numbers, and timestamps, REGEXEXTRACT extracts target data without relying on rigid character positions:

1. To extract only the numerical order number from a complex invoice description like “Shipment dispatched for Order #98421 on Tuesday”:

=REGEXEXTRACT(A2, "d+")

2. To extract all phone numbers contained within a multi-line customer note, setting return_mode to 1 to spill every match horizontally across adjacent columns:

=REGEXEXTRACT(A2, "d{3}[-.]?d{3}[-.]?d{4}", 1)

Sanitising and Masking Strings with REGEXREPLACE

Cleaning messy data feeds frequently requires stripping unwanted punctuation, standardising formats, or masking sensitive personal information:

1. To remove all special characters and punctuation symbols from an address line, leaving only alphanumeric words and spaces:

=REGEXREPLACE(A2, "[^A-Za-z0-9 ]", "")

2. To mask credit card numbers or account numbers, retaining only the trailing four digits:

=REGEXREPLACE(A2, "d(?=d{4})", "*")

These formulas compute dynamically within the Excel worksheet engine, automatically recalculating whenever referenced source data updates.