Regex Extractor & Tester
Free Regex Extractor & Tester: test a regex pattern live, then copy the matching =REGEXEXTRACT, =REGEXREPLACE or =REGEXTEST formula straight into Excel.
of 4 rows
| Text | REGEXEXTRACT | REGEXTEST | REGEXREPLACE |
|---|---|---|---|
| Contact Jane Doe at jane.doe@example.com or (415) 555-2671 | jane.doe@example.com | TRUE | Contact Jane Doe at or (415) 555-2671 |
| Invoice sent to billing@example.co.uk yesterday | billing@example.co.uk | TRUE | Invoice sent to yesterday |
| Reach Bob at bob.smith+sales@example.com, phone 415.555.9082 | bob.smith+sales@example.com | TRUE | Reach Bob at , phone 415.555.9082 |
| No email on this line | #N/A | FALSE | No email on this line |
Real-World Data Cleaning Examples
REGEXEXTRACT Use Cases
- Pull email addresses out of free text —
=REGEXEXTRACT(A2, "[\w.+-]+@[\w-]+\.[\w.]+") - Pull invoice numbers out of order notes —
=REGEXEXTRACT(A2, "INV-\d{4}-\d{5}")
REGEXREPLACE Use Cases
- Strip every phone number down to digits —
=REGEXREPLACE(A2, "\D", "") - Remove HTML tags pasted from a web page —
=REGEXREPLACE(A2, "<[^>]+>", "")
REGEXTEST Validation
- Validate a SKU matches ABC-1234 —
=REGEXTEST(A2, "^[A-Z]{3}-\d{4}$") - Validate a 5-digit US ZIP code —
=REGEXTEST(A2, "^\d{5}$")
Need a broader cleanup pass first — trimming, de-duplicating, changing case? Run your data through the Data Cleaner and finish here with a regex extraction. See also the Text to Columns Splitter, First & Last Name Splitter, Remove Extra Spaces, Duplicate Remover, Case Converter and Character & Word Counter tools.
How it works
In Excel or Google Sheets, test a regex pattern live, then copy the matching =REGEXEXTRACT, =REGEXREPLACE or =REGEXTEST formula straight into Excel.
Test the pattern against a handful of real rows first — a pattern that matches too much or too little fails silently, not with an error.
Type =REGEXEXTRACT(, click the cell holding the text, then the pattern in quotes, and close the brackets.
Keep backslashes single inside the quotes: \d stays \d, it does not need doubling like it would in some other languages.
Add return_mode 1 as the third argument to spill every match down the column instead of only the first.
Swap REGEXEXTRACT for REGEXTEST to validate a format, or REGEXREPLACE to clean or redact instead of extract.
What this does
REGEXEXTRACT, REGEXREPLACE and REGEXTEST bring PCRE-style regular expressions to the formula language, replacing the nested FIND, MID and LEN stacks or a VBA function that free-text extraction previously needed. REGEXEXTRACT pulls out the part of a cell that matches a pattern, REGEXREPLACE swaps every match for something else, and REGEXTEST returns TRUE or FALSE for whether the pattern matches at all. All three share the same pattern argument and are Microsoft 365 only, among the newest functions in the formula language.
A worked example
A support column mixes free text with an email on every row, such as "Contact Jane at jane.doe@example.com about invoice INV-2024-00931". =REGEXEXTRACT(A2,"[\w.+-]+@[\w-]+\.[\w.]+") returns jane.doe@example.com on its own, and the same pattern inside =REGEXTEST(A2,"…") confirms which rows contain one before the extraction runs anywhere else. REGEXEXTRACT, REGEXREPLACE and REGEXTEST replace a nested FIND-MID-LEN stack or a VBA module with one pattern for the pulling-apart, cleaning and validating that free-text columns usually need.
Common mistakes
- Assuming the three functions exist everywhere — they need Microsoft 365, and most readers on a perpetual licence will not have them yet.
- Writing a pattern that also matches rows it should not, without checking a row where it should fail.
- Forgetting all three default to case-sensitive matching, and adding case_sensitivity 1 right after the pattern — it is the last argument, which means giving return_mode 0 first in REGEXEXTRACT or occurrence 0 first in REGEXREPLACE;
REGEXTESTtakes it third with nothing before it. - Reaching for a full pattern where
TEXTBEFORE,TEXTAFTERorTEXTSPLITalready answer a simple delimiter split.
FAQ
Which regex flavour do these functions use?
PCRE-style, close enough to the ECMAScript flavour most browser-based testers use that a proven pattern mostly transfers unchanged.
How do I return every match instead of only the first?
Add return_mode 1 as REGEXEXTRACT’s third argument, e.g. =REGEXEXTRACT(A2,"\d+",1), and the matches spill down the column.
Do I need Microsoft 365 for these?
Yes — REGEXEXTRACT, REGEXREPLACE and REGEXTEST are 365-only. Older perpetual versions need VBA, Power Query, or nested FIND/MID/LEN instead.
Can I extract a capture group instead of the whole match?
Yes — wrap the part you want in parentheses and pass return_mode 2 to REGEXEXTRACT.
Why does a pattern work in a browser tester but not in Excel?
The most common cause is an escaped backslash: type the pattern exactly as shown in the formula, in quotes, with a single backslash before d, w, s and similar tokens — Excel does not need it doubled.
What are the limitations of testing a pattern outside Excel first?
A browser-based tester runs the ECMAScript regex engine, not PCRE2, so a handful of advanced features (such as possessive quantifiers or atomic groups) are not available there even though the pattern still works in a real cell — always confirm the formula in a workbook before relying on it.