REGEXTEST function
Quick answer
REGEXTEST checks whether text contains a match for a regular expression and returns TRUE or FALSE.
- Syntax
=REGEXTEST(text, pattern, [case_sensitivity])- Most common use
=REGEXTEST(A2,"^[A-Z]{2}\d{4}$")returns TRUE (Two capital letters, then four digits: TRUE)
Use it to validate formats such as order IDs, emails or postcodes, or as a condition inside FILTER and counting formulas. Matching is case-sensitive unless you set case_sensitivity to 1.
- Available in
- Microsoft 365 only
- Category
- Text functions
- Returns
- TRUE or FALSE.
- Google Sheets
- Google Sheets has REGEXMATCH instead, without the case_sensitivity argument.
Syntax
=REGEXTEST(text, pattern, [case_sensitivity])
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text to test. |
pattern | Required | The regular expression, in quotes, such as "^[A-Z]{2}\d{4}$". |
case_sensitivity | Optional | Optional: 0 (default) for case-sensitive, 1 to ignore case. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B2 | =REGEXTEST(A2,"^[A-Z]{2}\d{4}$") | TRUE | Two capital letters, then four digits: TRUE |
| B3 | =REGEXTEST(A3,"^[A-Z]{2}\d{4}$") | FALSE | Lowercase letters fail a case-sensitive test |
| B4 | =REGEXTEST(A3,"^[A-Z]{2}\d{4}$",1) | TRUE | The same ID passes when case is ignored |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Order ID | Result |
| AB1234 | TRUE |
| ab1234 | FALSE |
| AB12X4 | TRUE |
Practice REGEXTEST
Graded instantly, then re-checked on changed data.
Valid ticket IDs
B2, return TRUE if the ID in A2 matches that format and FALSE if not. Fill down to B7.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Always FALSE | The pattern is case-sensitive by default. Add 1 as the third argument, or use [A-Za-z]. |
| Matches more than you meant | Without ^ and $, the pattern can match anywhere inside the text: "\d{4}" is TRUE for "AB12345". |
| #NAME? | REGEXTEST is in Microsoft 365 only. Older versions show #NAME?; Google Sheets uses REGEXMATCH. |
Tips
- Count matching rows with =SUM(--REGEXTEST(A2:A100,"pattern")).
Questions
How do I check if a cell matches a pattern in Excel?
=REGEXTEST(A2,"pattern") returns TRUE or FALSE. For example "^\d{5}$" checks for exactly five digits.
Is REGEXTEST case-sensitive?
Yes, by default. Set the third argument to 1 to ignore case.
Answers that use REGEXTEST
Updated Checked by running: every formula on this page was run by our formula engine on .