Excel Function: REGEXTEST

The REGEXTEST function checks if part of a text matches a regular expression and returns TRUE or FALSE based on the result.

Using a regular expression (or "regex") allows you to perform complex tests with a short and efficient formula.

For example, it can be used to check if a phone number or email address is valid, whether a text contains specific values, or if it follows a defined structure, etc.

Usage:

=REGEXTEST(text, regex)

or:

=REGEXTEST(text, regex, case)

Understanding regular expressions

Writing a regular expression can seem complex and a bit daunting at first.

To help you get more familiar with them, this page (and the next ones) includes a series of examples using regular expressions, arranged in increasing difficulty.

Text contains

The regex "expression" searches for this word inside the text.

=REGEXTEST(A2,"expression")

The REGEXTEST function then returns TRUE or FALSE based on the result:

excel function regextest find word

Text starts with

The regex "^It’s" checks if the text starts with "It’s":

=REGEXTEST(A2,"^It’s")

excel function regextest find word start

The ^ character at the beginning of a regex indicates that the text must start with this expression.

Text ends with

The regex "n$" checks if the text ends with "n":

=REGEXTEST(A2,"n$")

excel function regextest find word end

The $ character at the end of a regex indicates that the text must end with this word.

Text alternatives

The regex "way|fun" checks if the text contains "way" or "fun":

=REGEXTEST(A2,"way|fun")

excel function regextest find word or

The | character indicates an alternative (equivalent to "or").

Allowed characters

Character classes are defined using [] and specify which characters are allowed. For example, the class [abc] means the character can be "a", "b", or "c".

The regex "[npt]ing" thus checks if the text contains "ning", "ping", or "ting":

=REGEXTEST(A2,"[npt]ing")

excel function regextest character class

A character class (e.g., [abc]) corresponds to a single character in the search.

Allowed character ranges

To avoid listing the entire alphabet in a character class, you can define character ranges using the - character.

For example:

[a-z]: lowercase letter in the range a-z
[A-Z]: uppercase letter in the range A-Z
[a-zA-Z]: letter in the range a-z or A-Z
[0-9]: digit in the range 0-9

The regex "^[A-Z][1-6]" checks if the text starts with an uppercase letter followed by a digit from 1 to 6:

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

excel function regextest character class range

Disallowed characters

To specify a negation (in other words, "all characters except"), add a ^ at the beginning of the class.

The regex "[^6-9]$" checks if the text ends with any character except a digit in the range 6 to 9:

=REGEXTEST(A2,"[^6-9]$")

excel function regextest character class negation