REGEX

The REGEX function enables usage of regular expressions in other text and conditional functions. It can be used with all functions that expect a condition (IF, COUNTIF, …) or a string match (SUBSTITUTE, TEXTBEFORE, …). When not used as a condition or to match text, REGEX returns the underlying regular expression as a string value.

REGEX(regular-expression-string, case-sensitive)

regular-expression-string: A string value representing a regular expression.

case-sensitive: An optional modal value that determines whether the regular expression should be considered case-sensitive or not.

Case-sensitive (TRUE or omitted): The regular expression should be considered case-sensitive.

Ignore case (FALSE): The regular expression should not be considered case-sensitive.

Notes

The regular-expression-string has to conform to the ICU standard.
A REGEX expression can be concatenated with another REGEX, the same as with regular string values. If a REGEX is concatenated with a regular string, the result is a regular string and no longer a REGEX.

Examples
=COUNTMATCHES("To count all words, use regex.", REGEX("\w+")) returns 6. =TEXTBEFORE("Get all the text before the first numbers 12345 - and nothing after.", REGEX("[0-9]+")) returns "Get all the text before the first numbers". Let each cell in the range A1:A10 contain a word, 5 of which contain the letter a. =COUNTIF(A1:A10, REGEX("a+",FALSE)) returns 5. =SUBSTITUTE("example@email.com: John Appleseed", REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}") & REGEX(": *"), "") returns "John Appleseed". =SUBSTITUTE("john@appleseed.com", REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+)(\.[A-Za-z]{2,4})"), "$2@$1$3") returns "appleseed@john.com". =COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]")) returns 2. =COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]", FALSE)) returns 3. Let A1 be "Client email: example@email.com". =IF(COUNTMATCHES(A1, REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}")), "We have an email", "No email") returns "We have an email".

Examples

=COUNTMATCHES("To count all words, use regex.", REGEX("\w+")) returns 6.

=TEXTBEFORE("Get all the text before the first numbers 12345 - and nothing after.", REGEX("[0-9]+")) returns "Get all the text before the first numbers".

Let each cell in the range A1:A10 contain a word, 5 of which contain the letter a.

=COUNTIF(A1:A10, REGEX("a+",FALSE)) returns 5.

=SUBSTITUTE("example@email.com: John Appleseed", REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}") & REGEX(": *"), "") returns "John Appleseed".

=SUBSTITUTE("john@appleseed.com", REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+)(\.[A-Za-z]{2,4})"), "$2@$1$3") returns "appleseed@john.com".

=COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]")) returns 2.

=COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]", FALSE)) returns 3.

Let A1 be "Client email: example@email.com".

=IF(COUNTMATCHES(A1, REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}")), "We have an email", "No email") returns "We have an email".

Helpful?

Formulas and Functions Help

REGEX

Notes