Excel REGEX Functions: REGEXTEST, REGEXEXTRACT, REGEXREPLACE

Regex in Excel means three functions: REGEXTEST, REGEXEXTRACT and REGEXREPLACE. A regular expression (regex) is a short pattern that describes text, such as "four digits" or "anything before the @". With these functions you can check, extract and clean text that would need long FIND and MID formulas before. This guide explains the syntax, the pattern symbols you'll use most, and what to do if your Excel doesn't have them.

What are the REGEX functions in Excel?#

Excel's regex functions each take a piece of text and a pattern. They differ in what they give back.

Version note: Microsoft added these functions to Microsoft 365 in 2024, first for Insiders and then for everyone on current builds. They work in Excel for Microsoft 365 on Windows and Mac, and in Excel for the web. Excel 2019 and Excel 2021 don't have them. If you use Excel 2024 or another one-time-purchase version, type =REGEXTEST( in a cell to check: if Excel doesn't suggest it, or the formula shows #NAME?, your version doesn't support it.

The patterns follow a common regex style called PCRE2, so most patterns you find in regex tutorials work here too.

Regex pattern basics#

A pattern mixes plain characters, which match themselves, with special symbols. You only need a handful to start:

Patterns go in double quotes like any text in a formula. If a pattern needs a double quote, type it twice: "".

REGEXTEST: check if text matches a pattern#

REGEXTEST returns TRUE when the pattern appears in the text. Use it to check codes, IDs and formats. Say A2 holds INV-2024-0583:

=REGEXTEST(A2,"^INV-\d{4}-\d{4}$")

The result is TRUE. The pattern means: start of the text, INV-, four digits, a dash, four digits, end of the text. INV-24-583 would return FALSE. Without ^ and $, the pattern could match inside longer text.

Matching is case-sensitive by default, so inv-2024-0583 fails too. Add 1 as the third argument to ignore case: =REGEXTEST(A2,"^inv-\d{4}-\d{4}$",1). Combine REGEXTEST with IF or FILTER to flag or list the rows that pass.

REGEXEXTRACT: pull text out of a cell#

REGEXEXTRACT returns the part of the text that matches. If A3 holds Order 4821, 3 items:

The third argument, return_mode, picks what comes back: 0 (the default) is the first match, 1 is every match, and 2 is the capture groups of the first match. For a date written as 2024-11-03, =REGEXEXTRACT(A4,"(\d{4})-(\d{2})-(\d{2})",2) returns 2024, 11 and 03 in separate cells.

The result is always text, so a number on the left of the cell won't add up. Wrap it in VALUE or put -- in front. For the non-regex ways to do this, see how to extract numbers from text in Excel.

A practical example: to pull an email address out of a sentence like Contact: anna.lee@example.com, use =REGEXEXTRACT(A5,"[\w.+-]+@[\w-]+\.[\w.]+"). It returns anna.lee@example.com. Real email rules are more complex, so treat this as a quick extractor, not a full validator.

REGEXREPLACE: clean and reformat text#

REGEXREPLACE finds every match and replaces it. It is the regex version of SUBSTITUTE, and it can replace patterns, not just fixed text.

The first formula deletes every non-digit. The second turns any run of spaces into one space. The third uses capture groups: $1 is the first group in brackets and $2 is the second, so the name is flipped.

The optional occurrence argument chooses which match to replace. Leave it out, or use 0, to replace all of them. Use 1 for the first match only.

No REGEX functions? Use FIND, SEARCH and SUBSTITUTE#

Every version of Excel can do the common regex jobs with older text functions. The formulas are longer, but they are what most job tests expect.

SEARCH finds the position of text and ignores case, while FIND is case-sensitive. The SEARCH function guide and the article on FIND vs SEARCH for locating text show how to combine them with MID. SUBSTITUTE swaps fixed text: see the SUBSTITUTE function guide and SUBSTITUTE vs REPLACE. To strip unwanted characters, read how to remove characters in Excel.

For counting and summing, wildcards are a mini pattern language that works everywhere: =COUNTIF(A2:A20,"INV-*") counts IDs starting with INV-. The guide to wildcards in COUNTIF and SUMIF covers *, ? and ~.

Common REGEX errors and fixes#

Most problems come from the version, the pattern or the result type.

• #NAME? means your Excel doesn't have the function. Use the older formulas above.
• An error when nothing matches. REGEXEXTRACT can't return a match that isn't there. Wrap it in IFERROR: =IFERROR(REGEXEXTRACT(A2,"\d+"),"").
• FALSE when you expect TRUE. Check capital letters, since matching is case-sensitive by default, and check that ^ and $ aren't too strict.
• Numbers that won't add up. The result is text. Wrap it in VALUE.
• A full stop that matches everything. . means any character. Write \. for a real full stop.

Practise it#

Regex is powerful, but the building blocks are the classic text skills that job tests check. Practise FIND and SEARCH in a live spreadsheet: you'll pull order numbers out of text, just like the exercise here. Then practise cleaning messy text with TRIM and SUBSTITUTE. Each formula is checked as soon as you press Enter.