Google Sheets Function: REGEXMATCH

The REGEXMATCH function returns TRUE if a part of a text matches a regular expression, or FALSE otherwise.

Using a regular expression (also called "regex") allows for highly precise testing with few characters.

It can be used, for example, to verify if an email address or phone number is valid, if a text contains certain data, if a text follows a defined structure, and so on.

Usage:

=REGEXMATCH(text, regular_expression)


Understanding Regular Expressions Through Examples

Writing a regular expression is very particular and can be quite daunting at first glance.

Instead of a long theory on regular expressions, this page offers a series of examples using regular expressions of increasing difficulty to help you understand through examples.

Text Containing

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

The REGEXMATCH function then returns TRUE or FALSE depending on the result:

google sheets function regexmatch search word

Text Starting With

The regex "^This" checks if the text starts with "This":

google sheets function regexmatch search word beginning
The character ^ placed at the start of the regex indicates that the text should start with this word.

Text Ending With

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

google sheets function regexmatch search word end
The character $ placed at the end of the regex indicates that the text should end with this word.

Text Alternatives

The regex "ing|ange" checks if the text contains "ing" or "ange":

google sheets function regexmatch search word or
The character | indicates an alternative (equivalent to "or").

Character Alternatives

Character classes are defined between [] and indicate what the character alternatives are. For example, the class [npt] means the character can be either "n", "p", or "t".

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

google sheets function regexmatch character class
A character class (e.g., [abcd]) corresponds to a single character in the search.

Character Alternatives: Ranges

To avoid sometimes listing the entire alphabet in a character class, it is possible to specify character ranges using the - character.

For example:

The regex "[A-Z][0-5]" checks if the text contains an uppercase letter immediately followed by a digit ranging from 0 to 5:

google sheets function regexmatch character class ranges

Character Alternatives: Negation

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

The regex "[^5-9]$" therefore checks if the text ends with a character that is not in the range from 5 to 9:

google sheets function regexmatch character class negation