TL;DR
- Regex in Excel means three functions: REGEXTEST (TRUE or FALSE), REGEXEXTRACT (pull text out) and REGEXREPLACE (find and replace by pattern).
- Microsoft documents them for Excel for Microsoft 365 and Excel for Microsoft 365 for Mac. Older perpetual versions do not have them.
- Patterns use the PCRE2 flavor. Matching is case-sensitive unless you set the case argument to 1.
- Results come back as text, so wrap numeric extractions in VALUE before doing math.
- On older versions, use wildcards, text functions such as LEFT, MID and TEXTJOIN, or a VBA regular expression object.
Table of Contents
What regular expressions are, and how Excel supports them
A regular expression (regex) is a short piece of text that describes a pattern: "three digits, a dash, four digits" or "anything inside parentheses." Instead of writing the exact string you want, you write the shape of it, and the engine finds every piece of text with that shape.
For years, Excel had no native regex. The usual routes were VBA, add-ins or Power Query. That changed in 2024, when Microsoft added three regex worksheet functions. Today the practical answer to "how do I use regex in Excel" is to type one of those functions into a cell, like any other formula.
If you have used wildcards such as * and ? in Find and Replace, you already know the idea. Regex is the much more powerful version: you can say "one or more digits," "exactly five digits," or "a capital letter followed by lowercase letters." Our page on wildcard examples and practice is a good warm-up if patterns are new to you.
Which Excel versions have the regex functions
This is the first thing to check, because it decides everything else.
- Excel for Microsoft 365 (Windows) and Microsoft 365 for Mac: Microsoft's support page lists REGEXTEST as available in both.
- Older perpetual versions: the functions are not documented for them. Plan on the workarounds later in this guide.
- Rollout timing: Microsoft 365 features can reach people gradually depending on their update channel. A Microsoft Q&A thread on the Semi-Annual channel notes that Semi-Annual updates arrive twice a year, in January and July, and that no guaranteed Semi-Annual date had been published for the regex functions. If you type =REGEX and nothing autocompletes, update Excel first, then ask your IT team which channel you are on.
Before you share a workbook, remember that a colleague on an older version will see errors where your regex formulas sit. If the file goes to a mixed audience, paste values over the results or use one of the fallbacks below.
REGEXTEST: check whether text matches a pattern
The syntax is REGEXTEST(text, pattern, [case_sensitivity]). It checks whether any part of the text matches the pattern and, per Microsoft's documentation, returns TRUE if a match is found and FALSE if not.
Say A2 holds an order code and you want to flag codes that follow the format AB-1234:
=REGEXTEST(A2, "^[A-Z]{2}-[0-9]{4}$")
Read it piece by piece:
- ^ anchors the match to the start of the text.
- [A-Z]{2} means exactly two capital letters.
- - is a literal dash.
- [0-9]{4} means exactly four digits.
- $ anchors the match to the end.
Without the
^ and
$ anchors, REGEXTEST returns TRUE if the pattern appears anywhere inside the text, so
xxAB-1234yy would pass. Anchors are how you turn "contains" into "is exactly."
Case sensitivity
REGEXTEST defaults to case-sensitive (0). Set the third argument to 1 for case-insensitive matching:
=REGEXTEST(A2, "^ab-[0-9]{4}$", 1)
This surprises people who are used to Excel's functions such as SEARCH, which ignore case. With regex, ab and AB are different unless you say otherwise.
Using REGEXTEST inside IF and FILTER
REGEXTEST returns TRUE or FALSE, which makes it a natural partner for the IF function:
=IF(REGEXTEST(A2, "^[A-Z]{2}-[0-9]{4}$"), "Valid", "Check format")
It also works as the include argument in dynamic array formulas. For example, =FILTER(A2:A500, REGEXTEST(A2:A500, "@example\.com$")) keeps only the rows that end in a given email domain. The backslash before the dot matters, and we cover why below.
REGEXEXTRACT: pull matching text out of a cell
The syntax is REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]). According to Microsoft's page, it extracts the strings that match your pattern, and the return mode lets you ask for the first match, all matches as an array, or specific capturing groups.
The return modes are:
| Return mode | What you get | Typical use |
| 0 (default) | The first match | Pull one ID, one number, one email |
| 1 | All matches, spilled as an array | Pull every number or every email in a paragraph |
| 2 | The capturing groups of the first match | Split a pattern into parts, such as area code and number |
Example: extract a number from text
A2 contains Invoice 48213 due Friday. To pull the invoice number:
=REGEXEXTRACT(A2, "[0-9]+")
[0-9] is one digit and
+ means "one or more," so the pattern grabs the whole run of digits. Both tokens are in
Microsoft's list of regex tokens, along with
* for zero or more and
. for any character.
The result is the text 48213, not a number. Microsoft notes that REGEXEXTRACT returns text and that you can convert it back with VALUE:
=VALUE(REGEXEXTRACT(A2, "[0-9]+"))
Skip that step and a SUM over the column will quietly ignore your "numbers." This is the most common regex mistake in spreadsheets.
Example: extract every email address
With a long note in A2 holding several addresses, set the return mode to 1:
=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}", 1)
The matches spill across cells. Join them back into one cell with TEXTJOIN:
=TEXTJOIN(", ", TRUE, REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}", 1))
That email pattern is deliberately simple. It catches normal addresses and will not validate every legal one, which is fine for cleaning a spreadsheet.
Example: capture the text inside parentheses
Parentheses in a pattern make a capturing group, and backslashes escape parentheses you want to match literally. To pull the department from Maria Lopez (Finance):
=REGEXEXTRACT(A2, "\(([^)]+)\)", 2)
The outer \( and \) match the literal parentheses. The inner unescaped pair is the group you want back. [^)]+ means "one or more characters that are not a closing parenthesis." With return mode 2 you get Finance without the brackets.
REGEXREPLACE: find and replace by pattern
The syntax is REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]). Microsoft describes it as searching the text for your pattern and replacing it with the replacement string. The occurrence argument lets you replace all instances by default or one specific instance.
Example: strip everything that is not a digit
Phone numbers arrive as (555) 123-4567, 555.123.4567 and 555 123 4567. To keep only digits:
=REGEXREPLACE(A2, "[^0-9]", "")
The caret inside the brackets means "not," so the pattern matches every character that is not a digit and replaces it with nothing. Wrap it in VALUE only if you need a true number, and be careful: a number format will drop leading zeros, which matters for ZIP codes and IDs. Leave those as text.
Example: collapse messy spacing
=REGEXREPLACE(A2, "\s+", " ")
This turns any run of spaces, tabs or line breaks into one space. For plain extra spaces, the TRIM function is simpler and faster. Reach for regex when the whitespace includes characters TRIM does not treat as spaces.
Example: reformat a date or a name
Capturing groups also work in the replacement. To flip Lopez, Maria into Maria Lopez:
=REGEXREPLACE(A2, "^(.+), (.+)$", "$2 $1")
The first group is the last name, the second is the first name, and $2 $1 reorders them. Test this on a few rows before filling down a whole column.
A starter pattern library
Most real work uses a small set of building blocks. Keep this table nearby.
| Pattern | Meaning | Example use |
| [0-9] | One digit | Find numbers |
| [a-z] | One lowercase letter | Find words |
| . | Any single character | Loose matching |
| * | Zero or more of the previous item | Optional runs |
| + | One or more of the previous item | Whole numbers, whole words |
| {4} | Exactly four of the previous item | ZIP codes, years |
| \d | A digit | Shorthand for [0-9] |
| \s | Whitespace | Clean spacing |
| ^ and $ | Start and end of text | Exact format checks |
| \. | A literal period | Domains, decimals |
The first four tokens come straight from
Microsoft's documentation. Microsoft also states that Excel's regex functions use the
PCRE2 flavor, so a pattern you tested in another PCRE-based tool will usually behave the same way here.
Why your regex formula returns the wrong thing
When a pattern misbehaves, the cause is usually one of these.
- Unescaped special characters. A dot, parenthesis, plus sign or question mark has a special meaning. To match the character itself, put a backslash before it: \. or \(.
- Case sensitivity. The default is case-sensitive. Add 1 as the case argument if Smith and SMITH should both match.
- Text versus numbers. Extracted digits are text. Use VALUE before summing, or you will get zeros or mismatched lookups.
- Missing anchors. A format check without ^ and $ passes anything that merely contains the format.
- Greedy matches. .+ grabs as much as it can. If you want the text up to the first comma, use [^,]+ instead of .+.
- Spill errors. Return mode 1 spills many results. If cells to the right or below are not empty, you get #SPILL!.
A useful habit: build the pattern on three or four sample cells, including an ugly one with extra spaces or odd punctuation, before you fill it across thousands of rows.
What to use if your Excel has no regex functions
You have options, and none needs a new license.
Wildcards. Functions such as COUNTIF, SUMIF, MATCH and XLOOKUP accept
* and
?. They cannot count characters or distinguish digits from letters, but for "starts with" and "contains" they are often enough. See the
wildcard guide and the
MATCH function.
Text functions. LEFT, MID, RIGHT, FIND, SUBSTITUTE and LEN solve most fixed-position problems. Our
text functions guide walks through them, and there are worked examples for extracting
the first word and
the last word from a cell.
VBA. Excel desktop can use the VBScript regular expressions library through VBA. One caveat from a developer walkthrough on
parsing substrings with VBA regex: if the workbook is opened on another computer, the Microsoft VBScript Regular Expressions 5.5 reference must be available and enabled there too. Macro-enabled files also trigger security prompts, which is one more reason to prefer the native functions when you have them.
Power Query. For heavy cleanup, Power Query is often the tool. A Microsoft Q&A answer on
regex and the web version says it is not available in Excel for the web, so check where your team actually opens the file.
Our perspective: learn the pattern, then the function
We built LogicExcel around one belief: you learn Excel by typing formulas and seeing what happens, not by watching someone else do it. Regex is a perfect example. The syntax of REGEXTEST takes about two minutes to read. The skill is in writing patterns, and that only comes from trying one, getting the wrong result, and working out why.
Our advice for anyone starting with regex in Excel:
- Start with REGEXTEST. A TRUE or FALSE answer is the fastest feedback loop for checking whether your pattern does what you think.
- Write patterns for the narrow case first. Match exactly four digits before you try to match every phone format in your company.
- Know the classic functions first. Many problems that look like regex problems are really TRIM, LEFT, MID or SUBSTITUTE problems, and those work in every version of Excel.
- Keep a test column of ugly sample data next to your formula column.
The analysts and managers we see get the most from regex are the ones who already have solid fundamentals in text functions and lookups. Regex then becomes a sharp extra tool rather than a black box.
> — Thomas Houza
Practice before you apply it to real data
Reading about patterns helps, but typing them is what makes them stick. On LogicExcel you can work through free interactive Excel exercises directly in your browser, with instant feedback and no account. If you want a structured path through text functions, lookups and logic before you tackle regex, the Excel courses take you from the basics upward. Pattern matching also appears in SQL, and our LIKE and wildcard practice is a quick way to see the same idea in another setting.
If you are preparing for a job test, remember that most employer assessments lean on core functions rather than newer ones. Try our free Excel test for interviews, and keep the functions cheat sheet open while you study.
FAQ
Is there a REGEXMATCH function in Excel?
No. REGEXMATCH is the name used in Google Sheets. In Excel, the equivalent is REGEXTEST, which returns TRUE or FALSE when the text matches the pattern. If you want the matching text itself rather than TRUE or FALSE, use REGEXEXTRACT.
How do I do a regex replace in Excel?
Use REGEXREPLACE with the text, the pattern and the replacement, for example =REGEXREPLACE(A2, "[^0-9]", "") to strip non-digits. The optional occurrence argument controls whether every match or one specific match is replaced. Each cell gets its own result, so the original data stays untouched until you paste values.
Can I use regex in Excel 2019 or Excel 2021?
The regex functions are documented for Excel for Microsoft 365 and Microsoft 365 for Mac, not for older perpetual versions. In those versions, use wildcards, text functions or a VBA regular expression object. Keep in mind that VBA solutions need macro-enabled files.
Why does REGEXEXTRACT give me text instead of a number?
Regex functions return text values. Wrap the formula in VALUE, as in =VALUE(REGEXEXTRACT(A2, "[0-9]+")), and the result behaves like a number. Leave IDs and ZIP codes as text so leading zeros survive.
Sources
Recommended
