The RegExpTest function

The RegExpTest function lets you evaluate a string against a regular expression to see if there is a match.

The RegExpTest function returns True if a regular expression matches any part of a string. See below for an example.

Learn more about regular expressions

Regular expressions are well described on the web, but there is not much Excel-specific information since Excel does not support regular expressions. One good starting point is the user guide for JavaScript in the Firefox browser.

There are many web pages that help you test and debug your regular expressions, e.g. regex101.com.

Function reference

Example

Screenshot of an example of using RegExpTest

In this example, the string in A4 is matched for the regular expression in A2. There is a match and the function returns TRUE.

This is what this RegExpTest function call will look like:

=RegExpTest(A2,A4,A8)

Format and parameters

=RegExpTest(regex, string, flags)

The string is matched against the regular expression, controlled by the optional flags. The function and its results are modeled after the Exact function in Excel:

  • If a part of the string matches the regex, the function returns True.
  • If both the regex and the string are empty or missing, the function returns True.
  • In all other situations, the function returns False.

You may want to hide the cell containing the RegExpTest function from the user. You can hide the entire row or column by right-clicking on it and then selecting Hide in the menu. You can also hide the cell containing the RegExpTest function with the Utility widget.

regex

Specify the regular expression you wish to match. Do not use any special delimiter for the regex, e.g. forward slash.

string

Specify the target string that you want to evaluate against the regular expression.

flags

Specify the flags that control the evaluation of the regular expression (see below). The flags parameter is optional.

Flags with regular expressions

The way the function operates can be controlled by the flags below.

  • If you include a flag in the string, it is turned On.
  • If you omit a flag from the string, it is turned Off.
  • If you provide an empty string, all flags are turned Off.
  • If you don’t provide a flag string, the default flags are imsu

g

Global search, matches the pattern multiple times. Off by default.

i

Case insensitive search, both a and A match a. On by default.

m

Multi-line mode where ^ matches the start of the string and $ matches the end of the entire string. Without this, each line in the string is treated separately. On by default.

s

Short for single line; the . wildcard also matches newline characters. On by default.

u

Treats a pattern as a sequence of Unicode code points. On by default.

y

Perform a sticky search that matches starting at the current position in the target string. Off by default.