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.
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}$"](https://support.content.office.net/en-us/media/591ec6a5-2c5a-42e6-b765-b09de6f9340c.png)
