RegexdrillRegex in Excel
Excel has native regex functions in Microsoft 365: REGEXTEST(text, pattern) returns TRUE or FALSE, REGEXEXTRACT(text, pattern, [return_mode]) pulls the first match, all matches or the capture groups, and REGEXREPLACE(text, pattern, replacement) substitutes using $1 for groups. Older Excel needs VBA (VBScript.RegExp); Google Sheets has REGEXMATCH, REGEXEXTRACT and REGEXREPLACE.
Syntax
=REGEXTEST(text, pattern, [case_sensitivity]) =REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) =REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])| Part | What it is |
|---|---|
text | the cell or string to search |
pattern | the regex as a text string (PCRE2 flavor; \d, \w, lookarounds all work) |
return_mode | REGEXEXTRACT: 0 first match (default), 1 all matches as a spilled array, 2 capture groups of the first match |
replacement | REGEXREPLACE: text that may use $1, $2 for groups |
case_sensitivity | 0 case-sensitive (default), 1 case-insensitive |
Examples
=REGEXTEST("jane@example.com", "^[^\s@]+@[^\s@]+\.[^\s@]+$")Result: TRUE
=REGEXEXTRACT("Order #4521 shipped", "\d+")Result: 4521
First match. Wrap in VALUE() to get a number.
=REGEXEXTRACT("a@x.com; b@y.com", "[\w.]+@[\w.]+", 1)Result: a@x.com b@y.com (spilled into two cells)
return_mode 1 returns every match as an array.
=REGEXREPLACE("555-123-4567", "(\d{3})-(\d{3})-(\d{4})", "($1) $2-$3")Result: (555) 123-4567
Common mistakes
- #NAME? means your Excel does not have the functions yet; they need a current Microsoft 365 build. Use VBA or Power Query otherwise.
- Using \1 in REGEXREPLACE; Excel uses $1.
- Expecting case-insensitive matching by default; pass 1 as the last argument or use (?i) at the start of the pattern.
- In Google Sheets, REGEXMATCH matches anywhere in the cell; add ^ and $ to validate the whole value, and note that REGEXEXTRACT returns only the capture group when one exists.
Practice Regex in Excel
These drills check your answer on live data, free, no account needed.
- Keep only the digitsRemove every character that is not a digit, so phone numbers in any format become plain digits.
- Simple email addressesThe whole line must match. Use this simplified rule: a local part of letters, digits, dots, underscores, plus signs or hyphens; one @; one or more domain labels of letters, digits or hyphens separated by dots; and a final label of two or more letters.
- US phone number formatsThe whole line must match. Accept (555) 123-4567, or ten digits split 3, 3 and 4 by the same separator throughout: a hyphen, a dot, or nothing.
Questions
Does Excel support regex?
Yes, in Microsoft 365 through REGEXTEST, REGEXEXTRACT and REGEXREPLACE (rolled out from 2024). Excel 2019, 2021 and LTSC do not have them; use VBA or Power Query there.
How do I use regex in VBA?
Set re = CreateObject("VBScript.RegExp"), then re.Pattern = "\d+", re.Global = True, and call re.Test(s), re.Execute(s) or re.Replace(s, repl). The VBScript flavor lacks lookbehind and named groups.
What are the regex functions in Google Sheets?
REGEXMATCH(text, pattern) for TRUE or FALSE, REGEXEXTRACT(text, pattern) for the first match or group, REGEXREPLACE(text, pattern, replacement) with $1 for groups. Sheets uses the RE2 flavor, which has no lookarounds or backreferences.
Related
- Regex in SQLRegular expressions in SQL by database: PostgreSQL ~ and regexp functions, MySQL REGEXP and REGEXP_REPLACE, Oracle REGEXP_LIKE, and what SQL Server offers.
- Regex for emailA practical email regex for JavaScript and other engines, how to validate a whole field, extract addresses from text, and why RFC-perfect patterns are a trap.
- Regex for numbers onlyThe ^\d+$ pattern for digits-only strings, stripping everything that is not a digit, extracting numbers from text, and patterns for signed and decimal values.
- Regex for phone numberPhone number regex for US numbers with dashes, dots, spaces or parentheses, an E.164 check for international input, and how to normalize before validating.