Testing Patterns With REGEXMATCH

Sometimes you do not want to check for one exact word. You want to ask a smarter question like does this cell contain any digits? or is this a valid email shape?

A regular expression (regex) is a tiny pattern language that describes the shape of text. Google Sheets lets you use it through three functions: REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE.

In this lesson we focus on the first one. REGEXMATCH simply answers a yes or no question: does the text match the pattern?

REGEXMATCH takes two arguments: the text to check and the pattern to look for. It returns TRUE if the pattern is found anywhere in the text, otherwise FALSE.

Note: this function lives in Google Sheets. Excel uses different tools, so we are firmly in Sheets territory here.

The simplest pattern is just a piece of literal text. The formula below checks if cell A2 contains the word cat.

By default regex matching cares about uppercase versus lowercase. The pattern cat will not match the text CAT.

To match either case you can list the options inside square brackets. [Cc]at matches both Cat and cat.

The formula below returns TRUE if A2 contains cat with the first letter in either case.

Regex gives you anchors to say where a pattern must sit.

• ^ means the start of the text
• $ means the end of the text

Without anchors, a match anywhere counts. With them you control position. The formula below is TRUE only when A2 starts with INV, which is handy for spotting invoice codes.

A character class describes a group of allowed characters. The most common shortcut is \d, which means any single digit from 0 to 9.

The pattern below asks: does A2 contain at least one digit anywhere? The + means one or more, so it is TRUE for order7 and FALSE for hello.

Imagine column A holds product names. Some include a size like Shirt 12 and some do not. You want a TRUE/FALSE flag for which ones contain a number.

Drag the formula below down column B. Rows with any digit show TRUE; pure text rows show FALSE. You can then filter on that column.

A few shortcuts cover most needs:

• \d any digit
• \w any letter, digit, or underscore
• \s any whitespace such as a space or tab
• . any single character at all

You can also build your own class. [A-Z] means any uppercase letter and [a-z] means any lowercase letter. The formula below checks whether A2 contains an uppercase letter.

Quantifiers say how many times the thing before them may repeat:

• + one or more
• * zero or more
• ? zero or one (optional)
• {3} exactly three

To check whether A2 is a five-digit ZIP code from start to end, combine anchors with an exact quantifier as shown below.

You can flag cells that look like an email address. A rough but practical pattern is: some characters, an @, more characters, a dot, then a couple of letters.

The formula below returns TRUE for sam@site.com and FALSE for not-an-email. It is not perfect validation, but it catches obvious mistakes in a contact list.

Because REGEXMATCH returns TRUE or FALSE, it slots straight into an IF. That lets you show a friendly message instead of a bare boolean.

The formula below labels each cell as Has number or Text only depending on whether it contains a digit.

Some characters are special in regex: . + * ? ( ) [ ] ^ $ \ and a few more. If you want to match them literally, put a backslash in front.

To check for a literal dot, write \. rather than just . (which would match any character). The formula below is TRUE only if A2 actually contains a period.

Test your understanding of REGEXMATCH and patterns.

You learned that REGEXMATCH(text, pattern) returns TRUE or FALSE in Google Sheets. Key ideas:

• Patterns describe the shape of text, not just exact words
• \d \w \s . are handy character shortcuts
• ^ and $ anchor to start and end
• + * ? {n} control how many times something repeats
• Escape specials like \. to match them literally

Next you will move from testing patterns to pulling the matching text out with REGEXEXTRACT.