The REGEXTEST function in Excel checks whether text matches a regular expression and returns TRUE or FALSE: =REGEXTEST(A2,"\d") tells you whether a cell contains at least one digit, and =REGEXTEST(A2,"^\d{5}$") whether it consists of exactly five digits, like a US ZIP code. It’s more flexible than SEARCH or COUNTIF with wildcards: the pattern describes the shape of the text — a phone number, an email, a word with digits — rather than specific characters. The function is available only in Excel for Microsoft 365.
| Function category | Text functions |
| Excel versions | Excel for Microsoft 365 (Windows and Mac) |
| Volatility | Non-volatile |
| Similar functions | REGEXEXTRACT, REGEXREPLACE, SEARCH, EXACT |
REGEXTEST function syntax
=REGEXTEST(text,pattern,[case_sensitivity])
- text — the cell or string to check;
- pattern — the regular expression in quotes;
- case_sensitivity — optional: 0 (default) — case-sensitive, 1 — case-insensitive.
The function looks for a match anywhere in the text. To check the whole cell, anchor the pattern to the start and end of the string with ^ and $. Patterns use the PCRE2 syntax shared by all three Excel regex functions; a cheat sheet of tokens is in the article on regular expressions in Excel.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | Pattern | Case | Result | What we check |
| 2 | Seattle, 1200 Pine St | \d | 0 | TRUE=REGEXTEST(A2,B2,C2) | contains a digit |
| 3 | Seattle, 1200 Pine St | ^[A-Z] | 0 | TRUE=REGEXTEST(A3,B3,C3) | starts with a capital |
| 4 | Seattle, 1200 Pine St | \bSt\b | 0 | TRUE=REGEXTEST(A4,B4,C4) | contains the word St |
| 5 | Seattle, 1200 Pine St | seattle | 0 | FALSE=REGEXTEST(A5,B5,C5) | case-sensitive |
| 6 | Seattle, 1200 Pine St | seattle | 1 | TRUE=REGEXTEST(A6,B6,C6) | case-insensitive |
| 7 | Seattle, 1200 Pine St | ^\d+$ | 0 | FALSE=REGEXTEST(A7,B7,C7) | digits only |
The pattern can live in a cell — handy for fine-tuning it by editing one cell. “seattle” isn’t found case-sensitively but is found with case_sensitivity 1. ^\d+$ requires the whole string to be digits.
Validate formats: phone, email, ZIP code
The main strength of REGEXTEST is checking that data follows the rules. Patterns with ^ and $ compare the whole cell, so an extra character at the start or end also counts as an error.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Value | Phone? | Email? | ZIP? |
| 2 | (206) 555-0142 | TRUE=REGEXTEST(A2,"^\(\d{3}\) \d{3}-\d{4}$") | FALSE=REGEXTEST(A2,"^[\w.+-]+@[\w-]+(\.[\w-]+)+$") | FALSE=REGEXTEST(A2,"^\d{5}(-\d{4})?$") |
| 3 | 206-555-0142 | FALSE=REGEXTEST(A3,"^\(\d{3}\) \d{3}-\d{4}$") | FALSE=REGEXTEST(A3,"^[\w.+-]+@[\w-]+(\.[\w-]+)+$") | FALSE=REGEXTEST(A3,"^\d{5}(-\d{4})?$") |
| 4 | info@semtools.guru | FALSE=REGEXTEST(A4,"^\(\d{3}\) \d{3}-\d{4}$") | TRUE=REGEXTEST(A4,"^[\w.+-]+@[\w-]+(\.[\w-]+)+$") | FALSE=REGEXTEST(A4,"^\d{5}(-\d{4})?$") |
| 5 | info@semtools | FALSE=REGEXTEST(A5,"^\(\d{3}\) \d{3}-\d{4}$") | FALSE=REGEXTEST(A5,"^[\w.+-]+@[\w-]+(\.[\w-]+)+$") | FALSE=REGEXTEST(A5,"^\d{5}(-\d{4})?$") |
| 6 | 98101 | FALSE=REGEXTEST(A6,"^\(\d{3}\) \d{3}-\d{4}$") | FALSE=REGEXTEST(A6,"^[\w.+-]+@[\w-]+(\.[\w-]+)+$") | TRUE=REGEXTEST(A6,"^\d{5}(-\d{4})?$") |
| 7 | 98101-2345 | FALSE=REGEXTEST(A7,"^\(\d{3}\) \d{3}-\d{4}$") | FALSE=REGEXTEST(A7,"^[\w.+-]+@[\w-]+(\.[\w-]+)+$") | TRUE=REGEXTEST(A7,"^\d{5}(-\d{4})?$") |
Phone — strictly (###) ###-####; email — a name, @ and a domain with at least one dot; ZIP — five digits with an optional +4 part. Use these formulas in Data → Data Validation → Custom to stop wrong values from being entered.
REGEXTEST in conditions, filters and counts
REGEXTEST returns a logical value, so it fits inside IF, can be counted with a double minus and passed to FILTER. If the first argument is a range, the function returns an array of TRUE/FALSE, one per cell.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Flag | Task | Result | |
| 2 | iPhone 15 case | has digits=IF(REGEXTEST(A2,"\d"),"has digits","") | How many with digits | 2=SUMPRODUCT(--REGEXTEST(A2:A6,"\d")) | |
| 3 | phone holder | =IF(REGEXTEST(A3,"\d"),"has digits","") | With digits (FILTER) | iPhone 15 case=FILTER(A2:A6,REGEXTEST(A2:A6,"\d")) | |
| 4 | USB-C cable | =IF(REGEXTEST(A4,"\d"),"has digits","") | 20W charger | ||
| 5 | 20W charger | has digits=IF(REGEXTEST(A5,"\d"),"has digits","") | |||
| 6 | Headphones | =IF(REGEXTEST(A6,"\d"),"has digits","") |
REGEXTEST over A2:A6 returns five TRUE/FALSE values; the double minus turns them into 1 and 0, and SUMPRODUCT adds them up. FILTER keeps the rows with a digit in the name — the result spills down.
Highlight cells by pattern
REGEXTEST also works in conditional formatting: Home → Conditional Formatting → New Rule → “Use a formula”. For example, to highlight rows with an invalid phone number, select A2:A100 and enter =NOT(REGEXTEST($A2,"^\(\d{3}\) \d{3}-\d{4}$")). The $ before A locks the column so the rule checks the same column in every row.
REGEXTEST vs SEARCH and COUNTIF
| Task | Without regex | With REGEXTEST |
|---|---|---|
| Cell contains a word | ISNUMBER(SEARCH("sale",A2)) | REGEXTEST(A2,"sale",1) |
| Cell contains any digit | COUNT(SEARCH({0,1,2,3,4,5,6,7,8,9},A2))>0 | REGEXTEST(A2,"\d") |
| Exactly 5 digits | AND(LEN(A2)=5,ISNUMBER(--A2)) | REGEXTEST(A2,"^\d{5}$") |
| Starts with a capital | EXACT(LEFT(A2),UPPER(LEFT(A2))) | REGEXTEST(A2,"^[A-Z]") |
SEARCH is enough to find a word. REGEXTEST wins when the shape of the text matters: the number of characters, their type and order.
If your Excel doesn’t have REGEXTEST
In Excel 2010–2024 without a Microsoft 365 subscription the formula returns #NAME?. The same check is available with the REGEXMATCH function from the free !SEMTools add-in: =REGEXMATCH(A2,"\d"). It has no case argument — matching is always case-sensitive, so list both variants for case-insensitive search: [Ss]eattle. The add-in uses the VBScript engine, which doesn’t support lookbehind (?<=…); the rest of the basic syntax is the same.
Why REGEXTEST gives wrong results: common errors
- #NAME?. The function isn’t available in your Excel version — use REGEXMATCH from !SEMTools.
- TRUE where you expected FALSE. The pattern matched part of the string: \d{4} also matches “12345”. Add ^ and $ to check the whole cell.
- A word in different case isn’t found. Matching is case-sensitive by default — set the third argument to 1.
- An error or an unexpected result. A special character isn’t escaped: write dot, plus, brackets and question mark with a backslash — \. \+ \(.
- Quotes inside the pattern. A double quote inside an Excel formula string is doubled: """" is a pattern of one quote.
REGEXTEST function — FAQ
What does the REGEXTEST function do in Excel?
It checks whether text contains a fragment matching a regular expression and returns TRUE or FALSE: =REGEXTEST(A2,"\d").
Which Excel versions have REGEXTEST?
Excel for Microsoft 365 for Windows and Mac. In other versions use REGEXMATCH from the !SEMTools add-in.
How do I check that the whole cell matches a pattern?
Anchor the pattern to the start and end: =REGEXTEST(A2,"^\d{5}$") — exactly five digits.
How do I make the check case-insensitive?
Set the third argument to 1: =REGEXTEST(A2,"seattle",1).
What is REGEXTEST called in other languages?
In Russian Excel it’s РЕГТЕСТ; in Google Sheets the equivalent is REGEXMATCH.
Check thousands of cells against a regular expression, filter or delete rows by pattern without formulas — in a couple of clicks with !SEMTools, in any Excel version.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: the REGEXTEST function in Excel
| Task | Formula |
|---|---|
| Contains a digit | =REGEXTEST(A2,"\d") |
| Exactly 5 digits | =REGEXTEST(A2,"^\d{5}$") |
| Case-insensitive | =REGEXTEST(A2,"seattle",1) |
| Count matching cells | =SUMPRODUCT(--REGEXTEST(A2:A100,"\d")) |
| Filter | =FILTER(A2:A100,REGEXTEST(A2:A100,"\d")) |
| Excel without 365 | =REGEXMATCH(A2,"\d") (!SEMTools) |
I hope you found what you were looking for on this page. REGEXTEST is the simplest way to check the shape of text; together with REGEXEXTRACT and REGEXREPLACE it covers almost every pattern-based text task.