Don’t forget to share this post

Google Sheets is a powerful and versatile tool that you can use with different types of data, including text. When working with text, you’re likely interested in more than just matching string literals (exact strings of characters). Fortunately, a regular expression - regex - allows you to search for abstract patterns in text.

Nowadays, regular expressions are very widely supported, not just in programming languages but also in text editors and text processing programs, as well as many other programs. Google Sheets has three functions that let you work directly with regular expressions.

This guide will focus on the first function, but you have examples of all three: REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE. After reviewing the function syntax, you have a reference guide with the basics for writing regular expressions. However, the best way to learn is to dive into the examples with step-by-step instructions. Once you get the hang of regular expressions, you’ll use all three functions like a pro.

What is the REGEXMATCH Function in Google Sheets?

The REGEXMATCH function in Google Sheets allows you to check whether cells contain a string matching a pattern given by a regular expression. The function returns TRUE if the pattern is matched and FALSE if it isn’t.

REGEXMATCH Function Syntax

The REGEXMATCH function has the following syntax:

=REGEXMATCH(text,regular_expression)
  • text: the text you want to search.
  • regular_expression: the string literal or string pattern that you want to match.

Special Characters for Regular Expressions

If you're going to write regular expressions to match string patterns rather than string literals, you need to know about the special characters and character classes. You can think of regular expressions as ways of describing abstract patterns in text. These patterns can combine string literals and special characters to represent different types or classes of characters.

Don’t worry about memorizing any of it, though. The best way to get the hang of regular expressions is to work through simple but detailed examples like those in this article. Below is a list containing different types of special characters you can use to build regular expressions.

Anchors

  • ^ matches the start of the text in which you want to match the regular expression.
  • $ matches the end of the text in which you want to match the regular expression.

Metacharacters

  • . matches any single character except a new line.
  • | matches the character before it or the character after it.
  • \ indicates that the character after it is being used as a literal and not as a special character.

Character Classes

  • [ ] matches any character from a set of characters.
  • [^ ] matches any character not contained in the set.

Shorthand Character Classes

  • \w matches any word character - i.e., any letter, digit, or underscore.
  • \W matches any non-word character - i.e., any character that isn’t a letter, digit, or underscore.
  • \s matches any whitespace character, including species, tabs, and line breaks.
  • \S matches any character that isn’t whitespace.
  • \d matches any digit from 0 to 9.
  • \D matches any character that isn’t a digit from 0 to 9.

Group

  • ( ) groups parts of your regular expression. For example, to apply a quantifier to a group of characters.

Quantifiers

  • {n} matches the preceding expression exactly n times.
  • {n,m} matches the preceding expression a minimum of n times and a maximum of m times.
  • ? match the preceding character or group 0 or 1 times.
  • * matches the preceding character or group 0 or more times.
  • + matches the preceding character 1 or more times.

How to Use REGEXMATCH in Google Sheets?

You can use REGEXMATCH to check whether a text contains a specific string or string pattern expressed as a regular expression. Pass the text or the cell containing it as the first parameter of the function and the regular expression as the second parameter: REGEXMATCH(text,regular_expression).

1. Type Function

In an empty cell, type the REGEXMATCH function.

REGEXMATCH in Google Sheets How to Examples Function
REGEXMATCH in Google Sheets: How to & Examples - Function

2. Add Text

Type the text between quotation marks or select the cell containing the text.

REGEXMATCH in Google Sheets How to Examples Add Text
REGEXMATCH in Google Sheets: How to & Examples - Add Text

3. Add Regular Expression

After the comma, type the regular expression between quotation marks or select the cell containing it.

REGEXMATCH in Google Sheets How to Examples Add Reg Ex
REGEXMATCH in Google Sheets: How to & Examples - Add RegEx

4. TRUE/FALSE Result

Close the parenthesis and press ‘Enter’ to see the result. Grab the fill handle and drag it down to the last cell you want to check.

REGEXMATCH in Google Sheets How to Examples Result
REGEXMATCH in Google Sheets: How to & Examples - Result

Examples of REGEXMATCH in Google Sheets

Below, you have examples of using REGEXMATCH to check for string patterns that combine literal characters with special characters.

How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

RegEx Example 1. Match Words

Follow the steps below to use REGEXMATCH to check a text and match any of the words you specify using the OR symbol (|).

  1. 1. First, type the regular expression into an empty cell to avoid worrying about quotation marks. In this case, you want to match one of the following words: red, blue, or green, so the regular expression is “Red|Blue|Green”
REGEXMATCH in Google Sheets How to Examples Write Reg Ex
REGEXMATCH in Google Sheets: How to & Examples - Write RegEx
  1. 2. Type the REGEXMATCH function and select the cell containing the text you want to check.
REGEXMATCH in Google Sheets How to Examples Function Text
REGEXMATCH in Google Sheets: How to & Examples - Function & Text
  1. 3. After the comma, select the cell containing the regular expression using absolute referencing.
REGEXMATCH in Google Sheets How to Examples Add Reg Ex 2
REGEXMATCH in Google Sheets: How to & Examples - Add RegEx
  1. 4. Close the parenthesis and press ‘Enter’. Grab the fill handle and drag it down to the last row.
REGEXMATCH in Google Sheets How to Examples Result 2
REGEXMATCH in Google Sheets: How to & Examples - Result

RegEx Example 2. Match Numbers

Follow the steps below to check whether a text contains any numbers.

  1. 1. In an empty cell, write the regular expression you want to use. In this case, you want to match numbers with one or more digits: “[0-9]+”
REGEXMATCH in Google Sheets How to Examples Write Reg Ex 3
REGEXMATCH in Google Sheets: How to & Examples - Write RegEx
  1. 2. Type the REGEXMATCH function and select the cell containing the text.
REGEXMATCH in Google Sheets How to Examples Function Text 2
REGEXMATCH in Google Sheets: How to & Examples - Function & Text
  1. 3. After the comma, select the cell with the regular expression using absolute referencing.
REGEXMATCH in Google Sheets How to Examples Add Reg Ex 4
REGEXMATCH in Google Sheets: How to & Examples - Add RegEx
  1. 4. Close the parenthesis and press ‘Enter’ to see the result. Grab the fill handle and drag it down to check the rest of the cells.
REGEXMATCH in Google Sheets How to Examples Result 3
REGEXMATCH in Google Sheets: How to & Examples - Result
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

RegEx Example 3. Starts with & Ends with

Follow the steps below to match based on the text’s start and end. In this case, I want to match sentences that begin with a capital letter and end with a period. In between, there can be any number of characters of any type.

  1. 1. Write the regular expression you want to use in an empty cell. In this case, you want an uppercase letter at the beginning, followed by any number of any type of character (^[A-Z].*). Additionally, you want the last character to be a period, so you must precede it with a backlash to use it literally (\.$). The complete regular expression is as follows: “^[A-Z].*\.$”.
REGEXMATCH in Google Sheets How to Examples Write Reg Ex 4
REGEXMATCH in Google Sheets: How to & Examples - Write RegEx
  1. 2. Type the REGEXMATCH function and select the cell containing the text.
REGEXMATCH in Google Sheets How to Examples Function Text 4
REGEXMATCH in Google Sheets: How to & Examples - Function & Text
  1. 3. Select the cell with the regular expression after the comma, using absolute referencing.
REGEXMATCH in Google Sheets How to Examples Add Reg Ex 5
REGEXMATCH in Google Sheets: How to & Examples - Add RegEx
  1. 4. Close the parenthesis and press ‘Enter’, then accept the suggested Autofill to copy the formula down.
REGEXMATCH in Google Sheets How to Examples Result 4
REGEXMATCH in Google Sheets: How to & Examples - Result

What is the Difference Between REGEXEXTRACT and REGEXMATCH?

There are three functions directly related to regular expressions in Google Sheets. While the REGEXMATCH function checks whether or not a string pattern is present in a text, REGEXEXTRACT extracts the matching string from the text. If you want to replace the matching string, you can use REGEXREPLACE.

REGEXMATCH

REGEXMATCH in Google Sheets How to Examples REGEXMATCH
REGEXMATCH in Google Sheets: How to & Examples - REGEXMATCH

REGEXEXTRACT

REGEXMATCH in Google Sheets How to Examples REGEXEXTRACT
REGEXMATCH in Google Sheets: How to & Examples - REGEXEXTRACT

REGEXREPLACE

REGEXMATCH in Google Sheets How to Examples REGEXREPLACE
REGEXMATCH in Google Sheets: How to & Examples - REGEXREPLACE

Conclusion

As you have seen, regular expressions offer an effective and efficient method for pattern matching. The strict and versatile syntax allows you to describe complex, abstract patterns that combine fixed and variable elements. Their compact and abstract nature can make them look scary at first, but now that you know how to read them, you can see how powerful they are. In Google Sheets, you can use regular expressions WITH the three REGEX functions, which can be combined with other formulas and built-in functions.

You now know about the REGEXMATCH function in Google Sheets and the related functions REGEXEXTRACT and REGEXREPLACE. You know how to use all three functions using simple regular expressions to find exact and partial matches. You have step-by-step instructions on matching one word from a list, matching numbers, and matching the start and end of a string. Finally, you have a reference guide summarizing the syntax for regular expressions so that you can work on more complex expressions.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 24 2023, Updated Jun 26 2023