Expert guide
Excel REGEX Functions: REGEXTEST, REGEXEXTRACT and REGEXREPLACE
Validate, extract and clean text with one formula instead of five nested ones
Call us at +1 (385) 386-3860
Short answer: Excel 365 has three regex functions. =REGEXTEST(text, pattern) returns TRUE or FALSE, =REGEXEXTRACT(text, pattern, [mode]) returns the first match, all matches, or capture groups, and =REGEXREPLACE(text, pattern, replacement) rewrites matching text. Patterns use PCRE2 syntax and are case-sensitive unless you set the last argument to 1. They replace most nested FIND, MID and SUBSTITUTE formulas. For data that needs cleaning every time it arrives, build it into automated imports.
Before regex arrived in Excel, extracting an invoice number buried in a line of text meant a formula like MID(A2, FIND("INV-", A2), 10) that broke whenever the number was a different length. Validating an email address was close to impossible. Regular expressions describe the shape of text rather than its position, so one short pattern handles every variation.
The Three Functions and Their Arguments
=REGEXTEST(text, pattern, [case_sensitivity])
→ TRUE / FALSE
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
return_mode 0 = first match (default)
1 = all matches (spills)
2 = capture groups of the first match (spills)
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
occurrence 0 = replace all (default), n = replace only the nth match
replacement can use $1, $2 ... to insert capture groups
case_sensitivity 0 = case-sensitive (default), 1 = ignore caseRegex Building Blocks You Will Actually Use
| Token | Matches | Example |
|---|---|---|
| \d | Any digit | \d{5} = five digits |
| \w | Letter, digit or underscore | \w+ = a word |
| \s | Whitespace | \s+ = one or more spaces |
| [A-Z] | Any character in the range | [A-Z]{2} = two capitals |
| . | Any character (escape as \. for a real dot) | a.c matches abc |
| + * ? {n,m} | One or more, zero or more, optional, between n and m | \d{3,4} |
| ^ $ | Start and end of text | ^INV = starts with INV |
| ( ) | Capture group | (\d+)-(\d+) |
| | | Or | Ltd|Limited |
| \b | Word boundary | \bInc\b |
15 Business Patterns Ready to Use
Validation with REGEXTEST
Email looks valid:
=REGEXTEST(A2, "^[\w.+-]+@[\w-]+(\.[\w-]+)+$")
US ZIP or ZIP+4:
=REGEXTEST(A2, "^\d{5}(-\d{4})?$")
UK postcode (common formats):
=REGEXTEST(A2, "^[A-Z]{1,2}\d[A-Z\d]?\s?\d[A-Z]{2}$", 1)
Canadian postal code:
=REGEXTEST(A2, "^[A-Z]\d[A-Z]\s?\d[A-Z]\d$", 1)
Product SKU like ABC-12345:
=REGEXTEST(A2, "^[A-Z]{3}-\d{5}$")Use these inside Data Validation (Data → Data Validation → Custom) to block bad entries at the source, or in a helper column with conditional formatting to highlight rows that need fixing.
Extraction with REGEXEXTRACT
Invoice number such as INV-2026-00431 from any text:
=IFNA(REGEXEXTRACT(A2, "INV-\d{4}-\d+"), "")
First email address in a block of text:
=IFNA(REGEXEXTRACT(A2, "[\w.+-]+@[\w-]+\.[\w.-]+"), "")
All dollar amounts in a note (spills):
=REGEXEXTRACT(A2, "\$[\d,]+(\.\d{2})?", 1)
Area code and number as separate columns (capture groups):
=REGEXEXTRACT(A2, "\(?(\d{3})\)?[\s.-]?(\d{3}[\s.-]?\d{4})", 2)
Domain from an email or URL:
=IFNA(REGEXEXTRACT(A2, "(?<=@|//)([\w-]+\.)+[a-z]{2,}", 0, 1), "")Cleanup with REGEXREPLACE
Collapse repeated spaces:
=REGEXREPLACE(TRIM(A2), "\s+", " ")
Keep digits only (phone numbers, IDs):
=REGEXREPLACE(A2, "\D", "")
Format 10 digits as (555) 123-4567:
=REGEXREPLACE(REGEXREPLACE(A2, "\D", ""), "^(\d{3})(\d{3})(\d{4})$", "($1) $2-$3")
Remove legal suffixes from company names:
=TRIM(REGEXREPLACE(A2, "\b(Inc|LLC|Ltd|Limited|Corp)\.?$", "", 0, 1))
Swap "Surname, First" to "First Surname":
=REGEXREPLACE(A2, "^\s*([^,]+),\s*(.+)$", "$2 $1")Regex in XLOOKUP and XMATCH
XLOOKUP's match_mode argument accepts 3 for regex matching. This lets you look up the first row whose text matches a pattern, for example the first transaction description that looks like a payroll provider.
=XLOOKUP("(?i)gusto|adp|paychex", tblBank[Description], tblBank[Amount], "None", 3)Let AI Write the Pattern, Then Test It
Regex is the perfect job for an AI assistant: the rules are strict and well documented, and the output is short enough to test fully. Give it examples of text that should and should not match.
Write a regex for Excel's REGEXEXTRACT (PCRE2) that extracts purchase order numbers. Should match: PO 44812, PO#44812, P.O. 44812, po-44812 Should NOT match: POS 44812, PORT 448 Return only the digits using a capture group, and explain each part.
Put the should-match and should-not-match examples in a column and apply REGEXTEST to all of them before using the pattern on real data.
Remember that in Excel formulas a regex backslash is typed once: \d, not \\d.
Watch for greedy patterns: .* matches as much as possible; use .*? to stop at the first match.
Regex vs Other Options
| Approach | Strength | Weakness |
|---|---|---|
| REGEX functions | One formula handles variable formats | Microsoft 365 only |
| TEXTBEFORE / TEXTAFTER / TEXTSPLIT | Simple and readable for fixed delimiters | Cannot describe patterns |
| FIND / MID / SUBSTITUTE | Works in every version | Long, fragile nested formulas |
| Power Query | Repeatable on every refresh; Text.Select, splitting | No native regex without custom functions |
| VBA RegExp | Works in older desktop Excel | Macro-enabled file required |
| AI extraction | Handles meaning, not just shape | Cost, speed, needs review |
Regex covers text with a predictable shape. When the meaning matters, such as deciding whether two company names are the same business, combine it with AI as described in cleaning messy Excel data with AI. To package your regex rules as named, reusable functions, see the Excel LAMBDA guide.
FAQ
Excel REGEX FAQ
Patterns, flags and errors