A Comprehensive REGEXMATCH Google Sheets Guide for 2024

The REGEXMATCH Google Sheets function is one of the most underrated functions of Google Sheets. Most users often choose not to use it as they think it will probably be too complex.

 

The presence of regular expressions in the formula often puts people off. However, with a little guidance, you can begin incorporating the function into your spreadsheets and reap its benefits.

 

In this tutorial, I will cover how to use the REGEXMATCH function in Google Sheets and show you some simple examples that will further help you understand how to use it in your day-to-day work.

What Does the REGEXMATCH Google Sheets Function Do?

The REGEXMATCH function belongs to Google Sheets’ suite of REGEX functions and functions like REGEXEXTRACT and REGEXREPLACE.

Its main task is to find a text string that matches a regular expression.

The function returns a TRUE if the text matches the regular expression’s pattern and a FALSE if it doesn’t.

Learn More

REGEXMATCH often has to be nested with other formulas, and learning about more than one at a time can be overwhelming. We’d recommend taking a full Google Sheets course to tackle this type of learning in the most effective way possible. Alternatively, you may find the perfect pre-made template(s) in our gallery. Remember to use the code SSP to save 50%.

Get Premium Templates

What Is a Regular Expression (REGEX)?

A regular expression is a text pattern. It is a sequence of characters, including symbols, letters, and numbers, that are combined to form a search string.

 

A regular expression is often characterized by certain special symbols or ‘metacharacters,’ representing either a single character, a sequence of characters, or one character in a set.

 

Here are some metacharacters that the REGEXMATCH function supports:

Metacharacter	What it Represents
^	The beginning of the string
$	The end of the string
.	A single character
?	Zero or one occurrence of a character or string
*	Zero or more occurrences of a character or string
+	One or more occurrences of a character or string
|	The Or operator
()	This holds a group of metacharacters inside it and represents that sequence of characters
[]	This holds a set of characters and represents any one of the characters inside it
[^]	This holds a set of characters and represents any one of the characters not listed inside it
	This is used to escape a special character

 

A regular expression containing a combination of these metacharacters and other alphanumeric and/or special characters can help you find specific characters, strings, or patterns in a text string.

 

For example, the regular expression “^gr(a|e)y$“ matches strings that start with ‘gr’ followed by either an ‘a’ or an ‘e’ and ends with a ‘y’.

Syntax of the REGEXMATCH Function

The syntax for the REGEXMATCH function is as follows:

REGEXMATCH(text, reg_exp)

Here,

• text is the string or value to be tested for whether it matches the regular expression.
• reg_exp is the regular expression the text is compared to.

Applications of the REGEXMATCH Function (Examples)

The REGEXMATCH function can be quite helpful when you want to search for the existence of a particular search string or pattern in the contents of a cell.

 

Here are some useful applications of the REGEXMATCH function:

1. You can use it to identify cells in a range that contain a particular letter, word, or phrase.
2. You can use it to identify cells that start with or end with a particular character or group of characters.
3. You can use it to find the Google Sheets REGEXMATCH exact match for text strings.
4. You can use it to go through a list of social media posts to identify posts that contain hashtags.
5. You can use it to find or validate email addresses, credit card numbers, IDs, etc., or any other format.

 

There are many other ways in which you can apply the REGEXMATCH function. Once you start getting comfortable with REGEXMATCH and regular expressions, you will find that the possibilities are endless.

 

Let us see how we can use REGEXMATCH in Google Sheets in each of the above applications

Using the REGEXMATCH Function to Identify Cells that Contain a Letter, Word, or Phrase

Let’s first see how you can use the Google spreadsheet REGEXMATCH function to identify if a cell contains a specific letter, word, or phrase.

 

If you want to know if cell A1 contains the word “good,” then use the REGEXMATCH function as follows:

=REGEXMATCH(A1,"good")

 

If you want to know if cell A1 contains any of the words ‘good,’ ‘wonderful,’ or ‘amazing,’ then that’s easy too.

 

We know that the metacharacter ‘|’ represents an Or operation. So you need to use the REGEXMATCH with a regular expression as shown below:

=REGEXMATCH(A1, "good|wonderful|amazing")

 

 

If you want to know if cell A1 contains numbers between 0-9, then you will need to use the square brackets as shown:

=REGEXMATCH(A1, "[0-9]")

In the below example, it returns FALSE, and there’s no number in cell A1.

 

 

In the below example, it returns TRUE, as there’s a number in cell A1.

Using the REGEXMATCH Function to Identify Cells that Start or End with a Specific Text

If you want to know if cell A1 starts with the word ‘you’ or ‘You,’ then you need to use the ‘^’ metacharacter (since it represents the beginning of a string) along with the ‘|’ metacharacter.

So the function you will need to use is:

=REGEXMATCH(A1, "^you|You")

 

 

If you want to know if cell A1 ends with the word ‘you’ or ‘You,’ then you need to use the dollar ($) metacharacter (since it represents the end of a string) along with the ‘|’ metacharacter.

 

So the function you will need to use is:

=REGEXMATCH(A1, "you|You$")

 

 

If you want to know if cell A1 starts with ‘You’ and ends with ‘good’ then you need to use a combination of the caret (^) and dollar ($) metacharacters:

=REGEXMATCH(A1, "^You[a-zA-Z ]+good$")

 

 

Here, we added “[a-zA-Z ]+” in the middle to represent any number of alphabets, both in lower and upper case, between the words ‘You’ and ‘great.’

 

Note that there is a space after the ‘Z,’ too, because we also want to allow spaces to be present in the sequence.

 

The plus sign (+) represents any number of letters and/ or spaces.

Using the REGEXMATCH Function to Find Cells with Numbers

The REGEXMATCH function can only be used in a cell that contains text; therefore, there’s no  REGEXMATCH for numbers in Google Sheets. If REGEXMATCH Google Sheets is used with numbers, it returns an error.

 

 

Luckily, there’s a loophole for this. You can convert your numbers to text format:

1. Select your data.
2. Go to “Number” > “Plain text.”

 

Now that our numbers are converted to text in our example, we can find cells that contain a number between 1 and 10 using the formula:

=REGEXMATCH(A2, "[1-9]")

 

The function will look at each individual digit in the cell, so even if a number is not between 1 and 9, it might still return TRUE.

Using the REGEXMATCH Function to Find the Exact Match of a String

Let’s say you want to know if cell A1’s contents exactly match the string “you.”

 

For this, all you need to do is use the REGEXMATCH function as follows:

=REGEXMATCH(A1,"^you$")

 

We get a FALSE in the result shown above because cell A1 contains the word “you” but does not contain only the word “you.”

Using the REGEXMATCH Function to Identify Hashtags among Social Media Posts

Let us say you have a set of social media posts in different cells of a column, and you want to match and identify only those posts with hashtags in them.

 

For this, you must find if each cell has the hashtag character (#) in it. So, you can perform the following steps:

1. Select the cell you want to get your TRUE/FALSE result in (B1 in our example).
2. Type the formula below, and don’t forget to put the regular expression within double quotes.

=REGEXMATCH(A1,”#”)

3. Press the return key.

4. You should now see a TRUE or FALSE value indicating if the string in A1 has a hashtag or not.

 

5. Double-click on the fill handle of B1.
6. You should now see the results for all the cells in the column.
   

   

Using the REGEXMATCH Function to Filter Criteria in Google Sheets

You can use the REGEXMATCH function together with the filter function. In our example, we can use the function to find data containing the name Smith and Bull only.

 

 

The REGEXMATCH function on its own will return either TRUE or FALSE:

=REGEXMATCH(A2, "Smith|Bull")

 

To pull out data, we’ll combine the REGEXMATCH formula with the filter formula. This will give us the formula:

=filter(A1:B, REGEXMATCH(A1:A, "Smith|Bull"))

 

 

This combination works almost the same as the index match formula.

Using the REGEXMATCH Function to Find Email Addresses in a Range

Let us see one final example of how powerful the REGEXMATCH function can be.

 

Let us suppose you want to find all the cells in a range (shown in the image above) that contain email addresses. For this, we first need to put together a regular expression for the pattern of an email address.

 

We know that:

• Every email address starts with a string containing letters, numbers, dot operators, hyphens, and underscores.
• This should then be followed by an ‘@’ symbol.
• This should be followed by another string containing letters and/ or numbers.
• This should then be followed by a dot operator (.)
• The email address should end with a string containing only letters.

 

For the above rules, we can formulate a regular expression as follows:

• Every email address starts with a string containing letters, numbers, dot operators, hyphens, and/or underscores. So, the regular expression can start with: ^[a-zA-Z0-9.-_]+.
• This should then be followed by an ‘@’ symbol. So, the expression can now be changed to: ^[a-zA-Z0-9.-_]+@.
• This should be followed by another string containing letters and/ or numbers. So, the expression can now be changed to: ^[a-zA-Z0-9.-_]+@[a-zA-Z0-9]+.
• This should then be followed by a dot operator (.). Since the dot operator might get mistaken for the dot metacharacter, we use a ‘’ metacharacter to escape it. So the expression can now be changed to: ^[a-zA-Z0-9.-_]+@[a-zA-Z0-9]+.
• The email address should end with a string containing only letters. So the expression can now be changed to: ^[a-zA-Z0-9.-_]+@[a-zA-Z0-9]+.[a-zA-Z]+$.

 

Note: a-z represents any lowercase character, while A-Z represents any uppercase character. 0-9 represents any numeric character between 0 and 9. The metacharacter plus (+) specifies that there can be one or more occurrences of the characters mentioned within the square brackets [].

 

So our final regular expression can be:

^[a-zA-Z0-9.-_]+@[a-zA-Z0-9]+.[a-zA-Z]+$

 

This is not perfect and completely foolproof, but to keep things simple, let’s go with it. It should work with our sample data.

 

Now, let us put this to use in our sample dataset. Follow these steps:

1. Select the cell you want to get your TRUE/FALSE result in (B2 in our example).
2. Type the formula, and don’t forget to put the regular expression within double quotes.

=REGEXMATCH(A2,” ^[a-zA-Z0-9.-_]+@[a-zA-Z0-9]+.[a-zA-Z]+$”).

3. Press the Return key.

4. You should now see a TRUE or FALSE value indicating if the string in A2 is an email address or not.

5. Double-click on the fill handle of B2.

6. You should now see the results for all the cells in the column.

 

Now, you can clearly see which of the cells contain email addresses. Subsequently, you could use a filter to display only the cells that contain email addresses if required.

These were a few simple examples of how the REGEXMATCH function can save time and increase efficiency.

Points to Remember

You must remember a few important points when using the REGEXMATCH function.

• This function only works with text input. It does not work with numbers.
• If you want to use numbers as input (for example, telephone numbers), you must first convert it to text using the TEXT function.
• Be careful with case letters because the REGEXMATCH function is case-sensitive. You’ll need to specify the correct case inside the regular expression or convert the entire input string to upper or lower case using the UPPER or LOWER functions.

 

The REGEXMATCH function can have numerous applications once you learn how to use it effectively, for example, using REGEXMATCH Google Sheets multiple words and Google Sheets conditional formatting REGEXMATCH.

 

You can also use the function together with the IF function in place of the IF Contains function in Google Sheets.

Frequently Asked Questions

What Type of REGEX Does Google Sheets Use?

Google Sheets uses the RE2 type of REGEX for regular expressions. This type does not work with Unicode character class matching.

What Is the Difference between REGEXTEXTRACT and REGEXMATCH?

The difference between the REGEXMATCH function and REGEXEXTRACT is that REGEXMATCH finds if a text string matches a regular expression while REGEXEXTRACT extracts the match from the text.

Is REGEX Case-Sensitive in Google Sheets?

Yes, the REGEX function in Google Sheets is case-sensitive; therefore, it differentiates between the upper and lower case.

Final Thoughts

In this guide, we’ve taken you through the REGEXMATCH Google Sheets function with plenty of examples.

Strong knowledge of regular expressions helps, and the best way to get a good hold of it is to practice. Play around with different regular expressions and see what results you get. You will be surprised by how useful the REGEXMATCH function can be once you start using it on your day-to-day spreadsheet data.

I hope you found this tutorial useful! You can also have a look at our guide for substring in Google Sheets.

Other Google Sheets Tutorials you may find useful:

• • How To Use IMPORTRANGE Function in Google Sheets (Examples)
  • How To Use the INDIRECT function in Google Sheets
  • How To Compare Two Columns in Google Sheets (for Matches & Differences)
  • Remove the First Character from a String in Google Sheets
  • How To Scrape Websites Using Google Sheets Formulas (Examples)
  • RegexReplace Function in Google Sheets