This post features 8 examples that will make your life infinitely easier as a digital marketer, showing how to use REGEXMATCH in Google Sheets GIF by GIF.
REGEXMATCH functions can be a lifesaver when you want to match and categorise words, numbers or symbols.
Standard Excel and Google Sheet formulas and filters can be limited, awkward and painfully slow to respond, especially if you’re working with big data sets.
However, by just applying some basic REGEX Google Sheets syntax, you can overcome these issues.
Just a heads up, I’m going to keep adding REGEXMATCH examples to this guide whenever I find really useful examples, so be sure to keep up to date!
REGEXMATCH examples
How do I use REGEXMATCH in Google Sheets?
The REGEXMATCH function belongs to Google Sheets’ suite of REGEX functions along with REGEXEXTRACT and REGEXREPLACE.
REGEXMATCH finds if a string of text matches a regular expression.
And returns a TRUE if the text matches the regular expression’s pattern and a FALSE if it doesn’t.
Before we jump into the examples, I want to quickly walk you through the syntax of a REGEXMATCH function.
It’ll help you better understand how to use REGEXMATCH functions in Google Sheets and the examples we’ll walk through, so bear with me for a second.
The syntax you’ll always need to use when writing a REGEXMATCH formula:
=REGEXMATCH(text, regular_expression)
- =REGEXMATCH is how you’ll always start your formula. It tells Google Sheets the function you want to use.
- Text
- Regular_expression – The regular expression to test the string of text against.
This will become clearer as we work our way through the examples.
REGEXMATCH Google Sheet examples
1. REGEXMATCH a single word
We’re going to start off nice and easy to get into the swing of things. Let’s match the keyword “dress”.
The formula we need to use:
=REGEXMATCH(A2,"dress")Let’s breakdown the formula for the first example.
Step 1 – =REGEXMATCH(text, regular_expression)
We need to start our formula with =REGEXMATCH, so Google Sheets understands the function type we’re trying to use.
Step 2 – =REGEXMATCH(text, regular_expression)
We then need to give Google Sheets the string of “text” that will be tested against the regular expression. We started looking at the text found in cell A2. Our formula now reads =REGEXMATCH(A2,
Step 3 – =REGEXMATCH(text, regular_expression)
This is where we add the regular expression to test the string of text against.
We need to tell Google Sheets what we want to look for, which is “dress” =REGEXMATCH(A2,"dress"). “dress” is our actual regular expression in this example.
You’ll notice that the formula returned both TRUE and FALSE. TRUE means the regular expression we added matched the pattern of text, whereas, FALSE didn’t.
2. REGEXMATCH contains
Instead of matching just one word in a list, there are times when you’ll want to match more. For example, I can quickly identify informational keywords in a list by grouping question words like “what”, “how” and “why”.
The syntax is similar to example 1, but with one main difference. We’re going to add the “|” operator.
The “|” operator means “or” in REGEX.
The formula we need to use:
=REGEXMATCH(A2,"what|how|why")This can also be pretty useful if you’re trying to group synonyms, abbreviations, acronyms and misspellings.
3. REGEXMATCH multiple words
Let’s kick it up a notch and refine our match to strings that contain specific combinations of words.
Let’s say I want to match strings that contain “summer” “red” and “dress”. You can combine REGEXMATCH and AND functions to solve this problem. Yep, you can combine REGEX and other functions. Pretty awesome, eh?
The formula we need to use:
=AND(REGEXMATCH(A2,"summer"),AND(REGEXMATCH(A2,"red"),AND(REGEXMATCH(A2,"dress")The AND function pretty much does what it says. We’re telling Google Sheets to match strings that contain “summer” AND “red” AND “dress”. You can add as many AND conditions as you want.
This formula allows you to segment and categorise keywords and if you have a big data set, it can be quicker and more efficient than using standard filters.
4. REGEXMATCH does not contain
But what if you want to exclude certain text that may still match against your multiple words formula?
Using the same example, let’s say you still want to match strings that contain “summer” “red” and “dress” but don’t contain “best”.
The formula we need to use:
=AND(REGEXMATCH(A2,"summer"),AND(REGEXMATCH(A2,"red"),AND(REGEXMATCH(A2,"dress"),NOT(REGEXMATCH(A2,"best")The syntax is pretty much the same as the last example, except we’ve added the NOT function.
We’re asking Google Sheets to match strings that contain “summer” AND “red” AND “dress”, but NOT “best”. You can see in the GIF that “best red dress for summer” returns FALSE.
Once again, this will allow you to go into deeper segmentation and categorisation without having to use a million filters.
5. REGEXMATCH numbers
You can identify strings that contain numbers. Maybe you want to quickly identify a bunch of URLs that have parameters?
The formula we need to use:
=REGEXMATCH(A2,"[0-9]")This is a pretty simple one. “[0-9]” will match any single number between 0 and 9.
6. REGEXMATCH word boundaries
You might be working with data that contains the same sequence of letters, but you only want to match specific text.
A textbook example is when you both have “men” and “women” in a list. Let’s say I only want to match strings that contain “men”, or “mens”.
The formula we need to use:
=REGEXMATCH(A2,"\bmens?")“\b” is called a word boundary and prevents matching any word that shares the same sequence of letters. In this case, I avoided matching “women”, or “womens”.
You’ll notice that I also added “?” at the end of “mens”. “?” acts as a catch-all for both the singular and plural version of “men”.
7. REGEXMATCH case sensitivity
You might work with data that requires you to take case sensitivity into account. Case sensitivity matters when using REGEX in Google Sheets, but there are workarounds.
If you’ve worked in SEO long enough you’ve likely had to scrape content from a website before. Perhaps you’ve wanted to find a list of pages that mention something specific? The output can sometimes be inconsistent.
In this example, I want to match all strings that reference “Download” and “download”. Notice the case difference between the two words.
The formula we need to use:
=REGEXMATCH(LOWER(B2),"download")The GIF will also show you what happens when you don’t add the LOWER function.
The LOWER function converts the text to lowercase, which allows me to match for both “Download” and “download”.
Without the LOWER function added to my REGEX, I wouldn’t be able to match “Download”.
8. REGEXMATCH IF conditions
If you’ve ever tried to categorise a list of URLs, then you’ll know what a pain it can be. Thankfully, we can combine REGEXMATCH and IF conditions in Google Sheets to speed up the process.
For example, instead of having subfolders e.g. /mens/jackets/, some websites might move everything straight to the root e.g. /mens-jackets.
The subfolder URLs have historical data you don’t want to discount, so you need to identify patterns to help categorise them.
In our above example, we can see that the old URLs end in a trailing slash, but the new URLs don’t. That’s our pattern right there!
The formula we need to use:
=IF(REGEXMATCH(A2,"/$"),"Old URL","New URL")To target URLs ending in a trailing slash, I added the “$” symbol as part of the regular expression. The “$” symbol will match text at the end of a string.
So, in my case, I want to match URLs that end in a trailing slash and use IF conditions to categorise them as an “Old URL”, or “New URL”. Just to be clear, you can use any naming convention to categorise things.
You could just as easily use the “$” symbol to match an ending word or letter in a string. Sometimes, singular and plural keywords hold different intent. You may want to identify these keywords and take a closer look.
The formula we need to use:
=IF(REGEXMATCH(A2,"s$"),"Plural", "Singular")It’s the same principle as the first example in this section, but we’ve just simply changed our regular expression.
Summary
This post walks you through a few REGEXMATCH Google Sheet examples that frees you from filters, allows you to precisely segment your data and cleverly match patterns.
Gone are the days of sluggish, limiting spreadsheets!
If you want to play around with and debug your REGEX formulas, then I recommend REGEX 101. You’ll need to select the Golang option, as that’s the type of REGEX Google uses in their products like Google Sheets and Google Analytics.
There are also a ton of REGEX cheat sheets out there. I like to glance at this REGEX cheatsheet.
If you’ve got any cool examples of using REGEXMATCH in Google Sheets that I haven’t listed, I’d love to see them. Drop a line in the comment section below.
And if you have questions, I’d love to help out, so feel free to ask away.
Happy REGEXing!
