A to Z of Excel Functions: The REGEXTEST Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the REGEXTEST function.

The REGEXTEST function

The term “regex” is an abbreviation of “regular expressions” and is frequently implemented in various programming languages such as C, C++, Java, Python, VBScript – and now, Excel.

Microsoft has stated that the version of Regex coming to Excel uses a “flavor” (sic) called PCRE2 (PHP>=7.3) for those that need to know the underlying technical stuff. 

Clearly, we need to learn a little about “regular expressions” before continuing.  Alternatively referred to “rational expressions” upon occasion, a regular expression is a sequence of characters that specifies what is known as a “match pattern” in text.  You have most likely used this functionality in Excel already, with features such as “Find and Relace” or by using the FIND or SEARCH functions in Excel.  The purpose of this function is to help you match, locate and manage text (strings) in Excel.

The text is obvious but understanding patterns requires you to learn the syntax for regular expressions.  Here is a crash course table, which summarises some – but not all – of the main elements, usually referred to as “tokens”.

Now we are all experts in regex, let’s consider the REGEXTEST function.  This function allows you to check whether any part of supplied text matches a regular expression (“regex”).  It will return TRUE if there is a match and FALSE otherwise.  The syntax of the REGEXTEST function is:

where:

• text: this is required, and represents the text or the reference to a cell containing the text you wish to match against
• pattern: this is also required.  This is the regular expression (“regex”) that you wish to match
• case_sensitivity: the final (optional) argument.  This determines whether the match should be case sensitive.  It has the following two [2] options:

This function always returns text values.  You may convert these results back to numerical values using the VALUE function.

Consider the following examples:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.