Excel Function: REGEXTEST (3/3)

Text containing a value then another

The regex "Regex.*fun" checks if the text contains "Regex" then "fun" later on the same line:

=REGEXTEST(A2,"Regex.*fun")

excel function regextest and 3

Parentheses

The regex "^\d{4}-\d{4}-\d{4}-\d{4}$" checks if the text exactly matches the desired structure (that is, 4x 4 digits separated by "-"):

=REGEXTEST(A2,"^\d{4}-\d{4}-\d{4}-\d{4}$")

excel function regextest structure number 3

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

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

=REGEXTEST(A2,"^\d{4}(-\d{4}){3}$")

excel function regextest parentheses 3

Parentheses also allow you to capture parts of the text; they will be used with the functions REGEXREPLACE and REGEXEXTRACT.

Parentheses and alternatives

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

To validate this regex the text must:

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

=REGEXTEST(A2,"^https://(www\.)?(excel|sheets)-pratique\.com")

excel function regextest url site 3

REGEXTEST

The REGEXTEST function returns TRUE or FALSE, so you can combine this function with others to return different values.

For example with the IF function:

=IF(REGEXTEST(A2,"^https://(www\.)?(excel|sheets)-pratique\.com"),"Authorized URL","Unauthorized URL")

You can even use it with your conditional formatting:

Assertions

Assertions allow checking a condition around an expression.

Replace "..." here with the useful expression:

(?=...): must be followed by
(?!...): must not be followed by
(?<=...): must be preceded by
(?<!...): must not be preceded by

The regex "\w+(?=,)" checks if there is at least one word of 1 or more characters directly followed by a comma:

=REGEXTEST(A2,"\w+(?=,)")

excel function regextest assertions 3

With the REGEXTEST function, assertions are not of great interest (in this case, we could simply have written \w+,). However, with the REGEXEXTRACT function they can prove more useful.

Backreferences

Backreferences allow reusing a piece of text captured earlier in the regex.

When you put a part of the expression between (), it becomes a capturing group, and you can refer to it later with \1, \2, etc., depending on the order of the groups.

The regex "(\b\w+\b).*\b\1\b" checks if a word appears at least twice (recall, \b marks the beginning and end of a word):

=REGEXTEST(A2,"(\b\w+\b).*\b\1\b",1)

excel function regextest backreferences 3

Sometimes it is useful to have non-capturing parentheses. To do this, add ?: after the opening parenthesis, for example: (?:...).

REGEXREPLACE function

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

Usage examples are available on the REGEXREPLACE function page.

REGEXEXTRACT function

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

Usage examples are available on the REGEXEXTRACT function page.