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.
Just as it can be concatenated with regular string values, a REGEX expression can be concatenated with another REGEX. However, if a REGEX is concatenated with a regular string, the result is a regular string and is 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@example.com: Marina Email", REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}") & REGEX(": *"), "") returns "Marina Email". =SUBSTITUTE("marina@example.com", REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+)(\.[A-Za-z]{2,4})"), "$2@$1$3") returns "example.com@marina.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: client@example.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@example.com: Marina Email", REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}") & REGEX(": *"), "") returns "Marina Email".

=SUBSTITUTE("marina@example.com", REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+)(\.[A-Za-z]{2,4})"), "$2@$1$3") returns "example.com@marina.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: client@example.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