Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web

The REGEXTEST function allows you to check whether any part of supplied text matches a regular expression ("regex"). It will return TRUE if there is a match and FALSE if there is not.

Syntax

The REGEXTEST function determines whether any part of text matches the pattern. 

The syntax of the REGEXTEST function is:

REGEXTEST(text, pattern, [case_sensitivity])

Argument

Description

text

(required)

The text or the reference to a cell containing the text you want to match against.

pattern

(required)

The regular expression ("regex") that describes the pattern of text you want to match.

case_sensitivity

Determines whether the match is case-sensitive. By default, the match is case-sensitive. Enter one of the following:

0: Case sensitive

1: Case insensitive

Notes: When writing regex patterns, symbols called ‘tokens’ can be used that match with a variety of characters. These are some simple tokens for reference:

  • “[0-9]”: any numerical digit

  • “[a-z]”: a character in the range of a to z

  • “.”: any character

  • “a”: the “a” character

  • “a*”: zero or more “a”

  • “a+”: one or more “a”

  • All regular expressions for this function, as well as REGEXEXTRACT and REGEXREPLACE use the PCRE2 'flavor' of regex.

  • REGEXEXTRACT always return text values. You can convert these results back to a number with the VALUE function.

Examples

Copy the example data and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Check various aspects of the string 'alfalfa'

Data

alfalfa

Formulas

Questions

=REGEXTEST(A2,"a")

Does it contain the letter 'a'?

=REGEXTEST(A2,"[a-z]")

Does it contain any lower case letters?

=REGEXTEST(A2,"[A-Z]")

Does it contain any upper case letters?

=REGEXTEST(A2,"[aeiou]")

Does it contain any vowels?

=REGEXTEST(A2,"[0-9]")

Does it contain any number digits?

The following illustration shows the results.

Using REGEXTEXT to answer various questions about the word 'alfalfa'

Check whether phone numbers have the specific format "(###) ###-####", using the pattern:

"^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$"

Note: Backslash "\" is used to 'escape' parentheses "()" and some other characters. In this pattern, "\(" is interpreted as "(" and "\)" is interpreted as ")"

Data

(378) 555-4195

+1(878) 555-8622

Formulas

=REGEXTEST(A2,"^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$")

=REGEXTEST(A2,"^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$")

The following illustration shows the result.

Using REGEXTEST to check whether phone numbers are in a specific syntax, with the pattern "^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$"

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.