Google Sheets Function: REGEXMATCH (3/3)

Email Address Test

A simplified regex to test the validity of an email address could look like "^[\w.-]+@[\w.-]{2,}\.[a-z]{2,}$".

To validate this regex, the text must:

^[\w.-]+: start with one or more alphanumeric or "_" or "." or "-" characters
@: be followed by "@"
[\w.-]{2,}: be followed by 2 or more alphanumeric or "_" or "." or "-" characters
\.: be followed by a dot
[a-z]{2,}$: be followed (and end with) by 2 or more characters from the a-z range

google sheets function regexmatch email 3

The shortcut \w equates to [a-zA-Z0-9_], so it does not include any accented characters.

Text Containing... Then...

The regex "expressions.*fun" checks if the text contains "expressions" followed by "fun" further on the same line:

Parentheses

The regex "^\d{4}-\d{4}-\d{4}-\d{4}$" checks here if the text exactly matches the desired structure (i.e., 4 groups of 4 digits separated by "-"):

google sheets function regexmatch license number 3

You can notice that the expression -\d{4} is repeated identically 3 times. To indicate that this part should be repeated 3 times, add parentheses followed by a quantifier (-\d{4}){3}.

The regex thus becomes "^\d{4}(-\d{4}){3}$":

google sheets function regexmatch parentheses 3

Parentheses also allow capturing parts of the text and will be used with functions like REGEXREPLACE and REGEXEXTRACT.

Parentheses and Alternatives

The regex "^https://(www\.)?(excel|sheets)-pratique\.com" checks if the text is a URL from "excel-pratique.com" or "sheets-pratique.com":

To validate this regex, the text must:

^https://: start with "https://"
(www\.)?: be optionally followed by "www."
(excel|sheets): be followed by "excel" or "sheets"
-pratique\.com: be followed by "-pratique.com"

google sheets function regexmatch url site 3

REGEXMATCH Function

As you know, the REGEXMATCH function returns TRUE or FALSE based on the text and the regular expression. You can thus combine this function with others to return different values.

For example, with the IF function:

=IF(REGEXMATCH(A2,"^https://(www\.)?(excel|sheets)-pratique\.com"),"Valid URL ;-)","Invalid URL...")

You can even use it for your conditional formatting:

REGEXREPLACE Function

The REGEXREPLACE function makes replacements in the text based on a regex.

Examples of use are available on the page of the REGEXREPLACE function.

REGEXEXTRACT Function

The REGEXEXTRACT function extracts one or more parts of the text based on a regex.

Examples of use are available on the page of the REGEXEXTRACT function.