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".

What is a regular expression?

A regular expression is a specially-crafted text string which can be used to find text strings within other text strings. They are very powerful, but can also be difficult to understand. There are entire books devoted to the topic and numerous online tutorials . This documentation provides a basic introduction, but you will need to consult other sources to truly master regular expressions.

Spreadsheets don't typically support regular expressions. However, we support the three functions REGEXMATCH, REGEXEXTRACT and REGEXREPLACE. REGEXMATCH returns whether a text string matches a regular expression, REGEXEXTRACT extracts parts of a text string using a regular expression and REGEXREPLACE replaces parts of a text string, again using a regular expression.

Regular expressions are part of lots of products and they often use dialects which aren't quite compatible with one another. We use the JavaScript dialect.

Using REGEXMATCH

The REGEXMATCH function is the most basic function that uses regular expressions. It returns TRUE if a text string matches a regular expression and FALSE otherwise. The first parameter is the text string which may or may not match the regular expression, which is given as the second parameter.

Does "bc" appear within "Abc"? The following formula returns TRUE to indicate that it does: REGEXMATCH ("Abc", "bc") REGEXMATCH ("Abc"; "bc").

Of course, you can determine the same thing with the FIND function. The true value in regular expressions is their support for special characters, which can match not just literal characters but entire classes of characters, such as all letters, all numbers or even all vowels, and more.

Matching a number

Some special characters are preceded by a backslash character, like "\d", which matches a single number. That means that REGEXMATCH ("5", "\d") REGEXMATCH ("5"; "\d") returns TRUE but REGEXMATCH ("a", "\d") REGEXMATCH ("a"; "\d") returns FALSE.

You can also spell out a number of permitted characters by enclosing them in brackets. "[0123456789]" is equivalent to "\d", meaning that REGEXMATCH ("5", "[0123456789]") REGEXMATCH ("5"; "[0123456789]") also returns TRUE.

Within brackets, you can use character ranges . "[0-9]" means "zero through nine" and is, again, equivalent to "[0123456789]" and "\d".

Matching a letter and whitespace

Character ranges work for letters too, so "[a-z]" matches all lower-case letters in the English alphabet, "[A-Z]" matches all upper-case letters and "[a-zA-Z]" matches all letters, regardless of case. "[^a-z]" is a negated character range (note the caret at the beginning) and matches any character which is not a lower-case letter. REGEXMATCH ("(", "[^a-z]") REGEXMATCH ("("; "[^a-z]") returns TRUE, because the opening parenthesis is not a letter.

There is also the special character "\w", which is equivalent to "[a-zA-Z0-9_]" and thus matches any letter, digit or underscore. "\s" matches any whitespace character, which is an invisible character, including space and tab characters.

Putting it all together, REGEXMATCH ("Abc10", "[A-Z][a-z][a-z]\d\d") REGEXMATCH ("Abc10"; "[A-Z][a-z][a-z]\d\d") returns TRUE, but REGEXMATCH ("Abc1", "[A-Z][a-z][a-z]\d\d") REGEXMATCH ("Abc1"; "[A-Z][a-z][a-z]\d\d") returns FALSE, as the regular expression expects two trailing numbers.

Repetition

To handle repetition, regular expressions offer "curly braces," which you use to specify the number of characters you expect. "\d{3"} is equivalent to "\d\d\d" and only matches three digits. "\d{3,5"} matches three digits, four digits or five digits, meaning that REGEXMATCH ("four", "[a-z]{3,5}") REGEXMATCH ("four"; "[a-z]{3,5}") returns TRUE, as "four" has four characters. "\d{3,"} matches at least three digits and "\d{,3"} matches at most three digits.

Use the special character "+" to indicate that one or more characters are accepted, "*" to indicate that zero or more characters are accepted and "?" to indicate that zero or one character is accepted. REGEXMATCH ("Abc10", "[a-zA-Z]+[0-9]+") REGEXMATCH ("Abc10"; "[a-zA-Z]+[0-9]+") returns TRUE, as does REGEXMATCH ("A1", "[a-zA-Z]+[0-9]+") REGEXMATCH ("A1"; "[a-zA-Z]+[0-9]+"), as the regular expression only stipulates that the text string must have one or more letters followed by one or more numbers.

"[a-zA-Z]*[0-9]*" (note that the "+" has been replaced by "*") actually accepts zero letters followed by zero numbers, meaning that REGEXMATCH ("1", "[a-zA-Z]*[0-9]*") REGEXMATCH ("1"; "[a-zA-Z]*[0-9]*") returns TRUE, as does REGEXMATCH ("", "[a-zA-Z]*[0-9]*") REGEXMATCH (""; "[a-zA-Z]*[0-9]*").

"[a-z]?\d" matches a letter followed by a number but also matches just a number, as the question mark makes the letter optional.

Use parentheses to repeat entire expressions. While "\d{3"} matches exactly three digits, "(\d[a-z]){3"} matches a number followed by a letter, but only if this combination is repeated three times. REGEXMATCH ("1a2b3c", "(\d[a-z]){3}") REGEXMATCH ("1a2b3c"; "(\d[a-z]){3}") returns TRUE, but REGEXMATCH ("1a2b", "(\d[a-z]){3}") REGEXMATCH ("1a2b"; "(\d[a-z]){3}") returns FALSE (the third repetition is missing).

Alternation

Parentheses can also be used to specify alternation in a regular expression in conjunction with the special character, "|". Alternation here means "either or," in the sense "match this text string or match that other text string."

As a simple example, REGEXMATCH ("this", "(this|that)") REGEXMATCH ("this"; "(this|that)") and REGEXMATCH ("that", "(this|that)") REGEXMATCH ("that"; "(this|that)") both return TRUE.

Matching any character

The period is a special character in regular expressions, as it matches anything. ".+\d" matches any number of characters followed by a number, meaning that REGEXMATCH ("test1", ".+\d") REGEXMATCH ("test1"; ".+\d") returns TRUE but REGEXMATCH ("0", ".+\d") REGEXMATCH ("0"; ".+\d") returns FALSE (there must be a character — any character — before the number).

"[^\d]+\d" is mostly equivalent to ".+\d" ("match at least one character which is not a number, followed by a single number"). Generally speaking, it runs faster, so we suggest that you try to use a negated character group instead of a period whenever possible.

Anchoring a match

The "^" and "$" characters are anchors which allow you to anchor your regular expression to the beginning of the text string or to the end of it. REGEXMATCH ("test0test", "\d") REGEXMATCH ("test0test"; "\d") actually returns TRUE, because there is a single number in the text string ("test" is ignored). REGEXMATCH ("test0test", "^\d$") REGEXMATCH ("test0test"; "^\d$"), on the other hand, returns FALSE, as the number in the regular expression is anchored both to the beginning of the text string and to the end of it, meaning that the "test" parts make the test fail.

The "\b" special character is also an anchor and matches between words. Use it to separate whole words from one another. REGEXMATCH ("testing", "\btest\b") REGEXMATCH ("testing"; "\btest\b") returns FALSE, because "test" needs to appear on its own, but REGEXMATCH ("this is a test", "\btest\b") REGEXMATCH ("this is a test"; "\btest\b") returns TRUE.

Escaping special characters

Finally, if you need to match literal text that is in conflict with the special characters supported by regular expressions, you need to escape that text by placing backslashes before the problematic characters. REGEXMATCH ("\d", "\d") REGEXMATCH ("\d"; "\d") returns FALSE, as "\d" isn't a number, but REGEXMATCH ("\d", "\\d") REGEXMATCH ("\d"; "\\d") returns TRUE, because the extra backslash means that you want to match a literal backslash followed by "d".

There are many features supported by regular expressions which we haven't covered here. If you're consulting other sources on regular expressions, remember that the flavor supported by this function is the JavaScript dialect.

Additional REGEXMATCH features

REGEXMATCH has additional features, which are unlocked by using its optional parameters.

Set the third parameter to TRUE to make the match case-insensitive. REGEXMATCH ("ABC", "bc") REGEXMATCH ("ABC"; "bc") returns FALSE because of the differences in case, but REGEXMATCH ("ABC", "bc", TRUE) REGEXMATCH ("ABC"; "bc"; TRUE) returns TRUE.

The fourth parameter determines how the "^" and "$" characters are interpreted. By default, "^" anchors the match to the beginning of the text string and "$" to the end of it. By setting the fourth parameter to TRUE, you can make these anchors relative to a line instead of the whole text string, if the text string consists of multiple lines.

Before demonstrating this fourth parameter, let's briefly discuss what a line in a text string really is. When you press Enter on your keyboard to get a new line, what you're doing is that you're entering a special character, a line break character . When your computer sees the line break character, it knows that the text that follows should be presented on a new line. You can produce a newline character using the CHAR formula function with the parameter 10, meaning that the formula "Line 1" & CHAR (10) & "Line 2""Line 1" & CHAR (10) & "Line 2" produces this text string:

 Line 1 Line 2  

REGEXMATCH ("\.$", "Test string.") REGEXMATCH ("\.$"; "Test string.") returns TRUE, because "Test string." ends with a period. (Remember that the period is a special character and must be escaped with a leading backslash in order to be interpreted literally.)

REGEXMATCH ("\.$", "Line 1." & CHAR (10) & "Line 2") REGEXMATCH ("\.$"; "Line 1." & CHAR (10) & "Line 2") returns FALSE, though, because "Line 2" doesn't end with a period and that's the end of the text string. To get REGEXMATCH to consider "Line 1." too and return TRUE, just set the fourth parameter to TRUE: REGEXMATCH ("\.$", "Line 1." & CHAR (10) & "Line 2", BLANK (), TRUE) REGEXMATCH ("\.$"; "Line 1." & CHAR (10) & "Line 2"; BLANK (); TRUE) or REGEXMATCH ("\.$", "Line 1." & CHAR (10) & "Line 2", MultipleLines: TRUE) REGEXMATCH ("\.$"; "Line 1." & CHAR (10) & "Line 2"; MultipleLines: TRUE) (with a named parameter).

Matching letters not in English

As we have seen, "[a-zA-Z]" can be used to match letters in English. Unfortunately, this only works for the 26 letters of the English alphabet. "\w" won't work either, as it's simply a concise way of writing "[a-zA-Z0-9_]".

To match a letter in any locale, write "p{L}" instead. To match any character which is not a letter, write "\p{^L}". REGEXMATCH ("日本語", "\w+") REGEXMATCH ("日本語"; "\w+") returns FALSE, but REGEXMATCH ("日本語", "\p{L}+") REGEXMATCH ("日本語"; "\p{L}+") returns TRUE.

"\p" enables characters to be chosen from various categories, where letters are just one category of many. Another useful category is made up of currency symbols like "$", "€" and "¥". Match currency symbols using the "Sc" category, meaning that REGEXEXTRACT ("$42", "\p{Sc}+") REGEXEXTRACT ("$42"; "\p{Sc}+") returns "$".

Counting array elements

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

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