REGEXTEST Function

The REGEXTEST function provides a way to test for specific text defined by a "regex" pattern. The result from REGEXTEST is TRUE or FALSE. You can think of REGEXTEST as a major upgrade to simpler functions like FIND and SEARCH, which can be used to test a cell for specific text. While these functions can perform primitive tests – FIND is case-sensitive but does not support wildcards, SEARCH supports wildcards but is not case-sensitive – they are no match for REGEXTEST, which can define tests using the full power of regular expressions. With regex, you can easily test for numbers, upper and lower case letters, exact quantities of certain characters, and for specific character sequences that follow a pattern.

Regular expressions ("regex" for short) are a powerful tool for pattern matching and text manipulation. Regex patterns can match simple strings or very specific sequences like phone numbers, email addresses, dates, and other text that has an identifiable pattern. Compared to the simple wildcards provided in older versions of Excel, regular expressions are a huge upgrade in functionality. They make it possible to match text in extremely specific ways that were impossible before now. While regex itself can be intimidating to new users, it's a very powerful language. As you become more comfortable with the syntax, you can tackle increasingly complex data challenges.

Code example

To use REGEXTEST, provide text and a pattern. For example, the formulas below show how REGEXTEXT can be used to test the text in A1 for a number or an uppercase character:

=REGEXTEST(A1,"[0-9]") // test for a number
=REGEXTEST(A1,"[A-Z]") // test for an uppercase character

REGEXTEST returns TRUE or FALSE. If we use REGEXTEST to test for "a" in "apple", it returns TRUE:

=REGEXTEST("apple","a") // returns TRUE

If we use REGEXTEST to test "apple" for a single digit between 0-9, it returns FALSE:

=REGEXTEST("apple","[0-9]") // returns FALSE

Worksheet example

The worksheet below uses REGEXTEST to test the same string, "1apple23#z", with twelve different patterns. In each formula, the text string comes from column B, and the pattern comes from column D. The formula in cell F5, copied down, is:

=REGEXTEST(B5,D5)

Let's review the formulas one by one. Note that the formulas below use literal strings instead of cell references for readability. In each case, the text being tested remains the same; only the pattern varies.

=REGEXTEST("1apple23#z","apple") // returns TRUE

REGEXTEST returns TRUE because the literal string "apple" appears in the text. Note that REGEXTEST automatically performs a "contains" type search.

=REGEXTEST("1apple23#z","a") // returns TRUE

REGEXTEST returns TRUE because the literal string "a" appears in the text.

=REGEXTEST("1apple23#z","A") // returns FALSE

REGEXTEST returns FALSE because it is case-sensitive by default, and the text does not contain an uppercase "A."

=REGEXTEST("1apple23#z","^1") // returns TRUE

REGEXTEST returns TRUE because the text string "1apple23#z" begins with a "1". The caret (^) is a metacharacter that matches the beginning of a text string.

=REGEXTEST("1apple23#z","^a") // returns FALSE

REGEXTEST returns FALSE because the text does not begin with "a".

=REGEXTEST("1apple23#z","Z$") // returns FALSE

REGEXTEST returns FALSE because the text does not end with an uppercase "Z". The dollar ($) is a metacharacter that matches the end of a text string.

=REGEXTEST("1apple23#z","[zZ]$") // returns TRUE

REGEXTEST returns TRUE because the text ends with "z" and the pattern "[zZ]$" matches "z" or "Z" at the end of the text.

=REGEXTEST("1apple23#z","[A-Z]") // returns FALSE

REGEXTEST returns FALSE because the text contains no uppercase character between "A" and "Z".

=REGEXTEST("1apple23#z","[0-9]") // returns TRUE

REGEXTEST returns TRUE because the text contains the digit "1". The pattern "[0-9]" matches any single digit between 0 and 9 anywhere in the text string.

=REGEXTEST("1apple23#z","[0-9]{2}") // returns TRUE

REGEXTEST returns TRUE because the text contains a two-digit number where each digit is between 0-9. The curly-brace syntax "{n}" specifies how many instances of a character must be present in the input for a match. The pattern "[0-9]{2}" means exactly two digits, each between 0-9.

=REGEXTEST("1apple23#z","[0-9]{3}") // returns FALSE

REGEXTEST returns FALSE because the text does not contain a three-digit number, even though it does contain three digits.

=REGEXTEST("1apple23#z","[[:punct:]]") // returns TRUE

REGEXTEST returns TRUE because the text contains the punctuation "#". The pattern "[:punct:]" matches a punctuation character like [!"#$%&'()*+,\-./:;<=>?@[\]^_`{|}~] in the text.

Alternation in Regex (OR Logic)

One problem you'll often encounter in regex is how to test for this or that within the same pattern. Alternation in regex allows you to check for multiple possibilities by using the pipe (|) symbol, which works like a logical "OR". By grouping alternatives within parentheses, you can match one of several patterns in a test string. For example, in the worksheet below, we want to test addresses for one of four US states: MN, MT, ND, and SD. The formula in D5, copied down, looks like this:

=REGEXTEST(B5,"\b(MN|MT|ND|SD)\b")

The \b symbol is a word boundary to ensure that only standalone instances of these abbreviations are matched.

Regex terminology and reference

Because Regex is essentially a mini programming language, it has its own vocabulary. Here is a list of some important terminology:

• Pattern - The actual sequence of characters that defines the regex.
• Literal - Characters in a regex pattern that match themselves. For example, in cat, the literals are c, a, and t.
• Metacharacter - Characters with special meanings in regex. For example, a period . matches any character, ^ matches the start of a string, $ matches the end of a string.
• Character Class - A set of characters enclosed in square brackets [] that matches any one of the characters inside. For example, [aeiou] matches any vowel.
• Quantifier - Specifies how many instances of a character, group, or character class must be present in the input for a match. For example, a* matches zero or more a's, a+ matches one or more a's, a{3} matches exactly three a's.

For details and more examples of useful regex patterns, see our Regex Reference Guide.