REGEXMATCH function

REGEXMATCH(text, regex, caseInsensitive?, multipleLines?) REGEXMATCH(text; regex; caseInsensitive?; multipleLines?)

text — Text or { Text }

The text string the regular expression is applied to.

regex — Text or { Text }

The JavaScript regular expression used to find text.

caseInsensitive — Logical or { Logical } (optional)

Whether the regular expression should ignore case. If omitted, FALSE is assumed.

multipleLines — Logical or { Logical } (optional)

Whether the regular expression anchors ^ and $ should be relative to a line of text or to the text string in its entirety. If omitted, FALSE is assumed.

Returns — Logical or { Logical }

Whether a given text string matches a JavaScript regular expression.

Returns whether a given text string matches a JavaScript regular expression. REGEXMATCH("Test123", "\w+\d\d\d")REGEXMATCH("Test123"; "\w+\d\d\d") returns TRUE, because "Test" matches the regular expression, meaning that it consists of one or more letters—"\w+"—followed by three digits (numbers)—"\d\d\d"—as specified by the regular expression "\w+\d\d\d".

Regular expressions are a powerful tool to search through and manipulate text, though they take time to master. For an introduction, refer to the blog post we wrote when Calcapp gained support for regular expressions.

Counting array elements

Use REGEXMATCH in conjunction with COUNTIF to determine the number of array elements which match a certain regular expression:

COUNTIF({ "123", "ab3" }, REGEXMATCH(Element, "\d\d\d"))COUNTIF({ "123"; "ab3" }; REGEXMATCH(Element; "\d\d\d"))

This formula returns 1, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three digits).

Examples

REGEXMATCH("Test123", "\w+\d\d\d")REGEXMATCH("Test123"; "\w+\d\d\d")

Returns TRUE, as "Test" matches the regular expression "\w+\d\d\d". Indeed, "Test123" consists of one or more letters, "\w+", followed by three digits (numbers), "\d\d\d".

REGEXMATCH(TextField1, "^\d{3}-\d{2}-\d{4}$")REGEXMATCH(TextField1; "^\d{3}-\d{2}-\d{4}$")

Returns whether the value of TextField1 matches a US Social Security number.

REGEXMATCH(TextField1, "^[^@]+@.+\.[^\.$]{2,}$")REGEXMATCH(TextField1; "^[^@]+@.+\.[^\.$]{2,}$")

Returns whether the value of TextField1 matches an email address. This regular expression is simpler and less complete than the one used internally by the ISEMAIL function.

REGEXMATCH("test0test", "\d")REGEXMATCH("test0test"; "\d")

Returns TRUE, because "\d" in the regular expression matches the sole number in "test0test", ignoring the two "test" strings.

REGEXMATCH("test0test", "^\d$")REGEXMATCH("test0test"; "^\d$")

Returns FALSE, because "\d" in the regular expression needs to match "test0test" in its entirety, because of the two anchors "^" and "$". The match fails because of the two "test" strings. For many more examples involving REGEXMATCH and other similar functions, refer to a blog post written at the time Calcapp's regular expression support was introduced.

COUNTIF({ "123", "ab3" }, REGEXMATCH(Element, "\d\d\d"))COUNTIF({ "123"; "ab3" }; REGEXMATCH(Element; "\d\d\d"))

Returns 1, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers).