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:
Text Starting With
The regex "^This"
checks if the text starts with "This":
^
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":
$
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":
|
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":
[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:
- [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
orA-Z
- [0-9]: digit in the range
0-9
The regex "[A-Z][0-5]"
checks if the text contains an uppercase letter immediately followed by a digit ranging from 0 to 5:
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: