What Microsoft Actually Shipped: Three Functions, Three Return Types
The three functions are one job each, and they never overlap. REGEXTEST asks a yes/no question about a cell, REGEXEXTRACT pulls matched text out of a cell, and REGEXREPLACE swaps matched text for something else. All three take the text first, the pattern second. REGEXTEST and REGEXREPLACE take a third argument, REGEXEXTRACT takes extra arguments for which match you want. If you remember the return type, you remember which one to reach for — a Boolean filter, a string, or a cleaned string.
The syntax, with the exact argument order:
=REGEXTEST(A2, "^[A-Z]{2}-\d{4}$") returns TRUE only if the whole cell is a two-letter, four-digit code like "AB-1042". The optional third argument is 0 for case-sensitive (the default) or 1 for case-insensitive. You use REGEXTEST for validation and filtering — no text comes back, just the verdict. In my experience it earns its keep in a helper column next to raw imports so you can see which rows fail before you build anything on top of them.
=REGEXEXTRACT(A2, "[A-Z]{2}-\d{4}") returns just the matched chunk, so "Order AB-1042 shipped" becomes "AB-1042". The optional return_mode argument takes 0 for the first match and 1 for every match, and 2 returns the capture groups from your parentheses. That third mode is the one people miss: if your pattern has (\w+)@(\w+\.\w+) and you pass 2, you get an array with the username and the domain separately.
=REGEXREPLACE(A2, "\D", "") strips every non-digit character and leaves a bare number. The replacement argument is required, and an empty string "" means delete. This is the function I use most for phone numbers, SKUs, and anything where the formatting is noise you want gone. Unlike SUBSTITUTE, you write one pattern instead of nesting ten of them.
Test every pattern on a single cell before you spill it down a column. Type the formula in one cell, look at the result, then drag. I keep a scratch sheet with four or five nasty sample values — a name with an apostrophe, an email with a plus sign, a phone number with an extension — and I run every new pattern against all of them first. Regex that passes one sample and fails the second is how you ship a broken cleanup.
The Twelve Pattern Tokens That Cover Most Cleanup Jobs
You do not need a regex book. Twelve tokens handle the overwhelming majority of spreadsheet text work, and you can learn them in an afternoon by breaking them into four groups: character classes, quantifiers, groups, and anchors. Everything below is written the way you would type it into an Excel formula, which means backslashes are doubled and the whole pattern sits in double quotes.
\d matches one digit, \w matches one word character (letters, digits, underscore), \s matches one whitespace character. Square brackets define your own set: [A-Z] is any capital letter, [aeiou] is any vowel, [^0-9] is anything that is not a digit. In Excel the pattern for a two-letter code followed by a hyphen and four digits is "[A-Z]{2}-\d{4}". Note the caret inside brackets means 'not' — outside brackets it means 'start of string', which trips up almost everyone the first week.
+ means one or more, * means zero or more, ? means zero or one (optional). Curly braces give an exact count: {4} is exactly four, {2,4} is two to four. "\d+" grabs an unbroken run of digits, which is how you pull 8821 out of "ID-8821". "\d{3,4}" matches a three- or four-digit number, useful when your phone list mixes formats. If you write "\d*" where you meant "\d+", you get a silent empty match on rows with no digits — the classic quiet bug.
Parentheses group part of a pattern so you can capture it or repeat it, and the pipe is an OR. "(Mr|Mrs|Ms)\.\s" matches any of the three title prefixes and captures the one you got. "(\d{3})-(\d{4})" captures the two halves of a phone number separately, which you then read out with REGEXEXTRACT's return_mode 2. Grouping is also how you keep a quantifier from eating too much: "(ab)+" is a repeating pair, "ab+" is one a followed by many b's.
^ anchors the pattern at the start of the text and $ anchors it at the end. "^\d+$" means the cell contains nothing but digits — if there is a space or a letter anywhere, it fails. This is the difference between 'find digits somewhere' and 'this cell is clean'. For validation columns I always anchor both ends, because unanchored patterns match partial garbage and report FALSE confidence.
The concrete payoff shows up on the messy exports that land in everyone's inbox. Suppose column A holds a Contact Email column scraped from a CRM, with values like "dana.okafor@northwind-traders.co.uk". The pattern "^[\w.+-]+@[\w-]+\.[\w.]+$" validates it, and "@([\w.-]+)" extracts the domain "northwind-traders.co.uk" for a domain count. On an Order Ref column holding "AB-1042", "XY-9087", and "note: pending", "^[A-Z]{2}-\d{4}$" tells you exactly which rows are real references. That is three cleanup problems gone with the tokens above and nothing else.
A common mistake is writing a pattern that matches on the sample you tested and nothing else. If you only test "dana.okafor@northwind-traders.co.uk", you will never discover that your email pattern rejects "sales+inbox@gmail.com" because it forgot the plus sign, or rejects a domain with a hyphens-only second level. Add the plus address and the weird TLD to your test set before you trust the formula. Two minutes of adversarial test values saves a broken report.
Pattern 1: Extract a SKU or Order Reference
The most common real job is pulling a code out of a free-text column because someone typed it inside a sentence. Imagine an Orders sheet where column A is Order Ref (clean codes), column B is Notes (human-typed), and the code sometimes only exists in B. Column B looks like "Reship of AB-1042, customer called", "AB-2210", and "No reference — see ticket". You want the code from B whenever A is blank.
In C2 write =IF(A2<>"", A2, IFERROR(REGEXEXTRACT(B2, "[A-Z]{2}-\d{4}"), "")) and fill down. The pattern [A-Z]{2}-\d{4} says: two capitals, a literal hyphen, four digits. On "Reship of AB-1042, customer called" it returns AB-1042. On "No reference — see ticket" there is no match, REGEXEXTRACT throws #N/A, and IFERROR turns that into an empty cell so your column stays clean.
Before building the extract column, run =COUNTIF(C2:C400, "?") style checks or simply add a validation column D with =REGEXTEST(B2, "^.*[A-Z]{2}-\d{4}.*$"). That tells you how many Notes rows actually contain a code. On a 400-row sheet I did last month, 61 rows had no reference at all, which was the real finding — the extraction was the easy part.
If a Note says "AB-1042 replaced by XY-9087", REGEXEXTRACT with return_mode 0 gives you the first code only. That may be wrong. Decide which one you want and write it into the pattern rather than hoping: use "AB-\d{4}" style or extract all matches with return_mode 1 into a spilled array and pick a column. Silently taking the first match is how a re-ship gets attributed to the original order.
Pattern 2: Clean Phone Numbers into One Format
Phone numbers arrive in eleven shapes from the same supplier. A Phone column holds "(415) 555-0132", "415.555.0132", "+1 415 555 0132", and "415-555-0132 ext 22". You do not want to write eleven SUBSTITUTE calls or four nested TEXT functions. You want one REGEXREPLACE that strips everything that is not a digit, then a small formatting step.
In B2 next to the Phone column write =REGEXREPLACE(A2, "\D", "") and fill down. \D is 'any character that is not a digit', so parentheses, spaces, dots, hyphens, and the plus sign all vanish. "+1 415 555 0132" becomes "14155550132". One formula, eleven formats, zero nested SUBSTITUTE calls.
If your rows all start with a 1 and you want a ten-digit US number, wrap it: =REGEXREPLACE(REGEXREPLACE(A2, "\D", ""), "^1(?=\d{10})", ""). The second pattern removes a leading 1 only when exactly ten digits follow. Without the lookahead-style length check, a genuine number starting with 1 would lose its first digit — this is the bug I hit the first time and it took me twenty minutes to spot because only three rows in the sheet had an eleven-digit value.
Once B2 holds ten digits, format it with a pattern: =REGEXREPLACE(B2, "(\d{3})(\d{3})(\d{4})", "($1) $2-$3"). The three groups capture the area code, prefix, and line number, and $1 $2 $3 reinsert them with punctuation. This is the practical reason to learn capture groups — you are not just finding text, you are rebuilding it. If your sheet mixes ten and eleven digits, add a REGEXTEST guard so short numbers skip the reformat.
This will break if your Phone column contains anything other than a number, like "n/a" or "call back". \D faithfully deletes the letters and you end up with an empty string that looks like a valid blank. Add a guard column first: =REGEXTEST(A2, "^[\d\s().+-]{10,}$"). It flags the rows that were never phone numbers, and you stop inheriting the supplier's typo as your data. I run that check before any regex cleanup on a phone field.
Pattern 3: Validate Emails and Pull Domains
A Contact Email column is where regex validation genuinely pays for itself, because a bad address means a bounced campaign and a wasted send. Say the sheet has 1,200 rows with columns Company, Contact Email, and Last Order. You want to know which addresses are structurally broken before you export them to your mailing tool.
Add a column with =REGEXTEST(B2, "^[\w.+-]+@[\w-]+(\.[\w-]+)+$"). Read it in pieces: one or more word characters, dots, plus signs, or hyphens, then @, then a domain label with no underscore, then at least one dot-suffix group. It accepts sales+inbox@gmail.com and dana.okafor@northwind-traders.co.uk, and rejects "dana@@northwind.co" and "dana@northwind". Fill it down and filter for FALSE.
Turn on AutoFilter on the validation column, choose FALSE, and copy those rows into a separate Bad Emails sheet. Do not touch the source. Every time I have deleted suspected-bad rows in place, at least one turned out to be a real customer with an unusual but valid address, and there was no undo. The validation column costs one formula and keeps the audit trail.
In an adjacent column use =REGEXEXTRACT(B2, "@([\w.-]+)") and fill down to get northwind-traders.co.uk and gmail.com as separate values. Because the pattern has a capture group, REGEXEXTRACT returns the group contents. From there a pivot table on the domain column tells you how much of your list is consumer mail versus corporate mail, which is a number marketing will actually ask for.
One warning I give every time this comes up: a regex that passes does not mean the mailbox exists. STRUCTURAL validation only tells you the address is shaped like an email. It will happily bless dana@northwind-traders.co often when the domain is a typo for .com. If you need deliverability, that is a different tool — an SMTP check — not a pattern. If your cleanup work is mostly this kind of multi-step reshaping, the approach in my Excel data cleaning guide covers the surrounding workflow that regex slots into.
Pattern 4: Pull the ID Out of "Northwind Traders (ID-8821)"
This is the example I open with because it is the exact shape of a thousand CRM exports. A Customer column holds Company and ID glued together in one string: "Northwind Traders (ID-8821)", "Fabrikam Logistics (ID-4470)", and one badly-formed row that says "Contoso (pending)". You need the ID in its own column for a lookup, and you need the company name too.
In B2 write =IFERROR(REGEXEXTRACT(A2, "ID-(\d+)"), "") and fill down. The pattern looks for the literal ID- and then captures one or more digits, so "Northwind Traders (ID-8821)" returns 8821 — just the number, no prefix, because the parentheses capture only the digits. The IFERROR handles the "Contoso (pending)" row, which has no digits after ID- at all.
The name is whatever is left before the bracket. Use =TRIM(REGEXREPLACE(A2, "\s*\(.*\)\s*$", "")). The pattern matches optional whitespace, an opening bracket, anything, a closing bracket, and the end of the string, then replaces it with nothing. "Northwind Traders (ID-8821)" becomes "Northwind Traders". Anchoring with $ matters — without it, a name containing its own parentheses gets chopped from the first bracket onward.
Put the ID in B and the name in C, then look up the ID against an Orders table with =XLOOKUP(B2, $F$2:$F$500, $G$2:$G$500, "No orders"). Now the messy CRM column feeds a clean report with no manual splitting. What used to be Data > Text to Columns plus a find-and-replace cleanup is two formulas that survive a refresh of the source.
Version Reality Check: Who Can Actually Use These
This is the part that decides whether the article is useful to you at all. The regex functions shipped in Microsoft 365, including Excel on the web, and they are not in Excel 2019 or Excel 2021. Open a workbook containing a REGEXEXTRACT on Excel 2021 and every one of those cells shows #NAME?. Not #VALUE!, not a wrong answer — the function simply does not exist on that build.
Go to File > Account and look at the Product Information panel. If it says Microsoft 365 Apps, you have them. If it says Excel 2019 or Excel 2021 as a perpetual licence with no updates, you do not. On Excel for the web, check that the workbook is stored on OneDrive or SharePoint — the web app is 365 and the functions generally work, though very new ones have appeared there second.
Those three also require a modern Excel build, which makes them a fallback for Excel 2021 users only in the sense that they are more widely known. TEXTBEFORE(A2, " (") and TEXTAFTER(A2, "ID-") would split "Northwind Traders (ID-8821)" without any pattern syntax, and TEXTSPLIT(A2, {" (", ")"}) splits on both delimiters at once. For most fixed-delimiter jobs these read more plainly than regex and are easier for a colleague to maintain.
If the file has to open in Excel 2019, the answer is not another function. It is =MID(A2, FIND("ID-", A2)+3, 4) for a fixed four-digit ID, plus nested SUBSTITUTE for character stripping and TRIM for cleanup. It is uglier and more fragile, but it works everywhere. I keep an old-Excel version of my phone cleanup as a single SUBSTITUTE chain for exactly this reason, and I hand it over when a client cannot upgrade.
The trap is building a beautiful regex-driven sheet on your 365 laptop and emailing it to a colleague on Excel 2021. They open a column of #NAME? and conclude you broke the file. If a workbook leaves your machine, ask what version the recipient runs first, and if you cannot confirm 365, keep the old MID/FIND version as a hidden backup sheet. I have done that handover twice and both times it saved a Monday morning.
Performance: When Regex Is the Slow Choice
Regex in Excel is genuinely slower than the plain text functions, and on big sheets the gap is not subtle. A REGEXREPLACE over 200,000 rows can take noticeably longer than the equivalent LEFT/MID chain, and if the formula is inside a volatile setup or gets recalculated on every change, the whole workbook starts to feel sticky. That is not a reason to avoid it; it is a reason to know where to place it.
Put a =NOW() in an unused cell, press F9 to force a full recalculation, and watch how long the status bar spins. Then temporarily delete the regex column and do it again. I did this on a 180,000-row Orders extract where a REGEXREPLACE column sat in the middle of a report, and the sheet went from roughly two seconds to roughly nine. Once I converted the column to values with Paste Special, it was back to two.
If your Order Ref is always in the first seven characters, =LEFT(A2, 7) is faster and simpler than any pattern. The same goes for pulling a fixed-position code out of a concatenated string. Reach for regex when the shape varies — when the code floats somewhere inside free text — not when the layout is rigid. A fixed layout is not a regex problem.
=REGEXEXTRACT(A:A, "[A-Z]{2}-\d{4}") looks tidy and is a performance disaster, because Excel has to evaluate a million cells through a regex engine. Point the formula at a bounded range like A2:A5000, or better, convert the source to an Excel Table and reference the column structured reference, which grows with the data instead of eating the sheet.
If the same cleanup runs every week on a fresh 100,000-row export, do not stack regex formulas down a column at all. Do it once in Power Query and hit Refresh. Power Query handles text transformation in a compiled step pipeline and it does not recalculate when someone types in cell Z9. My Power Query guide walks through the text-column steps, including split-by-delimiter and replace, which cover most of what people use regex for on a recurring feed.
The Five Mistakes That Actually Break People
Every one of these has cost me time on a real sheet. They are worth reading in order, because the first two account for most broken patterns I have debugged in other people's workbooks.
In "Item 12 of 30", the pattern <strong>\d+</strong> matches 12 correctly, but a pattern like (.*) in "(ID-8821) (ID-4470)" greedily matches everything from the first bracket to the last, so you get one huge match instead of two. The fix is to make it lazy with a question mark: (.*?) stops at the first opportunity. Lazy quantifiers — *?, +?, ?? — are the single most useful thing to learn after the basics. When a pattern returns text that is too long, greedy matching is almost always the reason.
Inside an Excel formula string, you write \d and \w with two characters each, because the double quote is the string delimiter and the backslash is written literally. What confuses people is that other tools — Python, JavaScript, a regex tester website — often want a single backslash or a different escape. If you paste a pattern from a regex playground into Excel and get a wrong result or an error, count the backslashes first. I keep a scratch cell with "\d{4}" in it purely as a visual reference.
A hyphen, a dot, a bracket, and a plus all mean something to the regex engine. If you want to match a literal period in "AB.1042", write "AB\.\d{4}" with a backslash before the dot — without it, the dot matches any character and "ABX1042" passes too. The characters that need escaping are . + * ? ^ $ ( ) [ ] { } | \ and inside brackets the hyphen. This is where I see the most silent wrong answers, because the pattern still returns something.
"\d{4}" will happily match the 2024 in a date, the 1042 in an order code, and the 8821 in an ID, all from the same column. If your pattern is loose, your extraction is loose and you will not get an error to warn you — you will get plausible wrong values. Anchor the pattern (^ and $) when you want the whole cell, and use a REGEXTEST validation column beside any extraction that feeds a number someone acts on.
REGEXTEST and friends expect text. If your Phone column is stored as numbers, a pattern with \D behaves oddly against a numeric cell, and if the column is formatted as text but contains numbers, mixed behaviour shows up. Convert the source column to text deliberately — TEXT(A2, "0") or a quick Text to Columns pass — rather than relying on whatever the export produced. Half the 'my regex does not work' cases I look at turn out to be number-versus-text, not regex.
When a pattern misbehaves and you cannot see why, reduce it. Delete the last third of the pattern and test again, then the last third of what remains. Regex bugs are almost always a single token that matches more or less than you think, and bisecting the pattern finds it faster than staring at it. I have narrowed a wrong pattern to one stray quantifier in under a minute with that method.
Build the Test Sheet and Prove It Works
Reading patterns does not stick; running them against your own messy export does. Set up one small sheet with four columns of deliberately nasty values and work through the four patterns above. It takes about twenty minutes and you will leave with formulas you can paste straight into next week's report.
Open a blank workbook and put these headers in row 1: Company, Contact Email, Phone, Notes. Fill ten rows with ugly values — a bracketed ID, a plus-address email, a phone with an extension, a code buried in a sentence, and at least one row that is genuinely empty. Naming the range as an Excel Table with Ctrl+T means your regex formulas auto-extend as you add test rows.
ID: =IFERROR(REGEXEXTRACT([@Company], "ID-(\d+)"), ""). Code: =IFERROR(REGEXEXTRACT([@Notes], "[A-Z]{2}-\d{4}"), ""). Phone: =REGEXREPLACE([@Phone], "\D", ""). Email OK: =REGEXTEST([@[Contact Email]], "^[\w.+-]+@[\w-]+(\.[\w-]+)+$"). Four columns, four functions, and you now have the whole toolkit running against real shapes.
Delete a hyphen from a code and watch the extraction return empty instead of a wrong value. Put a double @ into an email and watch REGEXTEST flip to FALSE. Change a pattern to greedy and watch it swallow a whole line. Knowing the failure mode of each function is what makes you fast later, because you will recognise the symptom before you debug the pattern.
Select the four new columns, copy, then Paste Special > Values. Every regex formula recalculating across a growing sheet costs time, and once the cleanup is verified there is rarely a reason to keep it live — unless the source data refreshes, in which case leave the formula in place and keep the range bounded. Decide deliberately which of the two you need. Before you go further with the surrounding workflow, the Excel shortcuts guide will save you real minutes on the mechanical parts.
