REGEXMATCH Google Sheets: A Beginner’s Guide

When you have large volumes of data, it’s important to be able to flag, filter, and validate text efficiently. By using built-in Google Sheets functions like REGEXMATCH to check whether a piece of text fits a specific pattern, you can check the format of customer entries, filter responses for certain keywords, identify duplicates, and more.

Build your Google Sheets skill set by learning about the REGEXMATCH function, how to use it, and advanced functionality that can help you streamline your workflow.

What is REGEXMATCH?

REGEXMATCH is a function in Google Sheets that checks whether a given cell’s content matches a specific pattern, known as a “regular expression.” If you have a match, the function will return TRUE. Otherwise, the results will be FALSE.

In its most basic form, the REGEXMATCH function is:

=REGEXMATCH(text, regular_expression)

In this formula, the text is the text you’re looking through for a match. You can either enter the body of text directly or reference the cell in Google Sheets that you want to check. The regular expression is the pattern you’re trying to find. 

What is an example of a REGEXMATCH? 

A simple example would be if you entered the expression: 

=REGEXMATCH(A1, “hello”)

With this line of text, you're checking whether cell A1 contains the word hello. If cell A1 only contains the word apple, then your expression would return FALSE. You can increase the complexity of your regular expression to match the start or end of a string, certain digits, alternate matches, and syntax formats like phone numbers or email addresses.

Basic REGEX patterns to know

Understanding basic REGEX patterns can open your search capabilities to more complex patterns, allowing you to unlock the full capabilities of REGEXMATCH. When creating your regular expression, you can combine matching patterns as building blocks to create the expression you’re looking for. Some patterns to start with include:

• . (dot): Match any single character. 

  • Example: “c.t” would match “cat” or “cut,” but not “cart” or “ct.”

• ^ (carat): Match the start of a string. 

  • Example: “^T” matches any string starting with “T,” like “Tiger” or “Thomas.” 

• $ (dollar sign): Match the end of a string. 

  • Example: “com$” matches any text string ending in “com,” like websites or email addresses.

• * (star): Match zero or more of the preceding characters. 

  • Example: “l*ol” would match “ll,” “lol,” “lool,” and “omg looooool” but not “lo” or “lal.” 

• + (plus sign): Match one or more occurrences of the preceding elements. 

  • Example: “ba+” would match “ba” or “bat,” but not “at” or “ab.” 

• {n}: Match exactly n occurrences of the character. 

  • Example: “u{2}” would match “vacuum” but not “hum.” 

• [abc]: Match any one character inside the brackets. 

  • Example: “[abc]at” would match “aat,” “bat,” or “cat,” but not “mat.” 

• (abc): Match multiple characters as a unit. 

  • Example: “(ha)+” would match “ha,” “haha,” or “hahaha.” 

This isn’t an exhaustive list, but it’s a good baseline to start creating your own custom regular expressions. You can combine elements and syntaxes to design creative searches. For example:

=REGEXMATCH(A1, “b.*g”)

In this function, you’re matching words starting with “b,” ending with “g,” and that contain any number of characters in between. The dot indicates a character in between, while combining the dot with the star means you can match with zero or more occurrences of said character in between. So you would return TRUE for “bg,” “big,” “brag,” and so on.

When should you use REGEXMATCH?

You can use REGEXMATCH any time you want to filter, flag, or validate data within your sheet. For example, if you want to filter customer reviews for ones that mention trouble with a product, you could create the function:

=REGEXMATCH(A1, “(break|fail|error|trouble|broken)”)

The parentheses mean you are grouping words, and the “|” in between the text strings acts as an “or,” allowing the function to return TRUE if any of the options are a match. 

Common examples of when you might use REGEXMATCH include:

• Validating customer email addresses

• Extracting customer phone numbers

• Locating a specific piece of information in your data set (e.g., customer birth date)

• Cleaning data by filtering out invalid entries

• Categorizing data (e.g., inventory management)

How to use REGEXMATCH in Google Sheets

You can build your REGEXMATCH functions with a few simple steps. As you gain confidence with REGEXMATCH, you can move to more advanced combinations of patterns. 

1. Define what you want to match.

Your first step is to understand exactly what you’re looking for and how to define it. If you’re looking for customer phone numbers, think about all of the possible formats. You might consider questions like: Are all customer phone numbers from the United States? Do some or all numbers have dashes in between number groups? 

Try to write down exactly what the options are so you can clearly define your regular expression.

2. Write your REGEXMATCH function.

Next, you’ll use the REGEXMATCH function to locate what you want to match. In one of the cells in your sheet, write =REGEXMATCH(text, regular_expression) and fill in the “text” portion with the cell you want to search. Then, build your regular expression to match what you defined in Step 1. 

You can test your expression on a few cells that you know should return “TRUE” or “FALSE” to check if it’s working correctly. 

3. Drag your formula to apply to all rows of interest.

Once you know your formula is working, you can click and drag the fill handle down to apply it across a column. This is useful if you want to apply it to multiple rows at once. 

Advanced REGEXMATCH techniques

Once you’re comfortable building and applying REGEXMATCH functions, you can combine them with other functions to extend capabilities. Conditional formatting and filtering are two common combinations with this function, each of which provides elevated data handling options. 

REGEXMATCH with conditional formatting

You can identify which cells you want to apply a certain function to by combining the IF function with REGEXMATCH. For example, you could write:

=IF(REGEXMATCH(A1, “com$”), “Yes”, “No”)

This would return “Yes” for entries that end in “com,” such as websites or email addresses, and “No” otherwise. Based on this, you could create functions that perform an action if the text is a match, and do not otherwise. 

REGEXMATCH with FILTER

You can also use the FILTER function to filter your data set based on whether it matches your regular expression. For example, you might write:

=FILTER(A1:A10, REGEXMATCH(A1:A10, “com$”))

This would filter cells A1 to A10 and return only the rows where the text ends in “com.” If you want to pull specific data from your database, this can help speed up that process.

Other REGEX functions

REGEXMATCH is one of three REGEX functions, each with its own functionality. All three functions are designed to recognize text patterns, but they do different things with this recognition. Outside of REGEXMATCH, the other two REGEX functions to know include REGEXEXTRACT and REGEXREPLACE.

REGEXEXTRACT

REGEXEXTRACT returns the first match of a pattern from the text. The baseline formula is:  

=REGEXEXTRACT(text, regular_expression)

This is similar to that of REGEXMATCH. For example, if cell A1 contained a body of customer information, like name, birth date, email address, and address, you could use REGEXEXTRACT to pull out the ZIP code with a function like: 

=REGEXEXTRACT(A1, “[0-9]{5}”)

This formula would match text of exactly five digits and return the first instance. 

REGEXREPLACE

REGEXREPLACE provides a way to replace text based on a regular expression. You would choose to use REGEXREPLACE when you need to alter text based on a specific pattern. The baseline formula is slightly different than other REGEX functions: 

=REGEXREPLACE(text, regular_expression, replacement)

If you wanted to replace all instances of “favourite” with “favorite,” you would write:

=REGEXREPLACE(A1, “favourite”, “favorite”)

Fortunately, using REGEX functions in Google Sheets versus Excel is similar, so once you’re comfortable creating the Google Sheets functions, you may be able to apply your knowledge to your Excel work as well. Excel functions include REGEXTEST, REGEXTEXTRACT, and REGEXREPLACE.

Common REGEXMATCH issues and alternatives

Something to pay special attention to with REGEXMATCH is that special characters, like “*” or “,” typically have a specific meaning within the regular expression. If you want to match these characters, you’ll need to remember to escape them with a backslash. So, if you were trying to match a “*” in your text, you would need to write “\*” to tell your formula that you’re referencing an actual star, rather than using it to symbolize something about your expression. Another common issue is not accounting for case sensitivity. REGEXMATCH, by default, is case-sensitive. If you want to match specific text regardless of capitalization, you may need to standardize your text before applying the function or create a function using the “or” to account for different variations. 

You can also make the REGEXMATCH function case-insensitive by using the (?i) flag before the regular expression:

=REGEXMATCH(A1, “(?i)puppy”)

This formula would return multiple case variations, such as “puppy,” “Puppy,” and “PUPPY.”

Summary of REGEXMATCH steps

Creating your first REGEX function doesn’t need to be overly complicated. You can start simple, check as you go, and explore different regular expressions to test out new ideas. You can create your function by following these steps:

1. Define what you’re looking for.

2. Write your REGEXMATCH function by following the regular expression syntax.

3. Check your REGEXMATCH function on a few cells.

4. Expand your function to all the cells you want to search.

5. Combine your function with FILTER and conditional formatting for advanced functions.

Learn more about REGEXMATCH in Google Sheets

REGEXMATCH in Google Sheets is a function that allows you to look for specific patterns within your text. When combined with other functions, you can streamline your workflow effectively and open yourself to using more advanced functions and automations to validate, manipulate, and analyze your data. 

Expand your data analysis skill set beyond Google Sheets with the Excel Skills for Data Analytics and Visualization Specialization on Coursera. In this Specialization, you can explore how to bring data to life using advanced Excel functions, creative visualizations, and powerful automation features.