How To Use REGEXMATCH in Google Sheets

REGEXMATCH in Google Sheets

Working with big data sets in spreadsheets is an everyday part of SEO.

What if I told you there was a way to speed up your data wrangling? Instead of sorting through data manually, wouldn’t it be good if you could use a formula to categories stuff?

Regex is a matching expression tool returning specific values (like true or false), whether it finds the expression or not.

The 3 main Regex formulas you can use on Google Sheets are:

• REGEXEXTRACT
• REGEXREPLACE
• REGEXMATCH

In this post, we’ll be checking out REGEXMATCH.

Let’s go!

What is REGEXMATCH?

The REGEXMATCH is one of the three regex functions (REGEXREPLACE + REGEXEXTRACT being the others) in Google Sheets.

It’s used to compare an input string to a regular expression and return whether that piece of text matches the regular expression. It returns either TRUE or FALSE.

The REGEXMATCH function has the following syntax:

=REGEXMATCH(text, regular_expression)

The input argument is the string to be matched to the regular expression and the second argument, regular_expression, is the regular expression itself.

When to use REGEXMATCH for marketing stuff?

• Categorising keywords.
• Categorising URLs.
• Doing some simple search intent analysis

How To Use The REGEXMATCH in Google Sheets

REGEXMATCH is very useful for combing through large keyword research docs.

For example, it can be useful if you want to check whether a text contains a certain word. You may want to know whether your most popular keywords contain a specific word such as the word “mexican”.

With a simple REGEXMATCH function, you can write the following logical test that returns TRUE or FALSE:

=REGEXMATCH(A2,"crust")

Dragging the formula down will look for TRUE/FALSE in the whole range:

NOTE: REGEXMATCH is case sensitive…

Important to note, that this regex matching is case-sensitive, so it distinguishes upper-case and lower-case letters.

If you change some of the keywords to upper-case “Crust”, the same function can’t find them since they don’t match the regular expression.

Fortunately, the regular expressions provide endless possibilities to customize your searches.

How to make REGEXMATCH match upper and lower cases:

• You can extend the above regex with an OR relation to search for “crust” or “Crust” in the strings.
• The “|” character stands for OR in regular expressions, so you can add more possible search options with it.
• Here’s what that looks like in our original formula:

=REGEXMATCH(A2,"crust|Crust")

More REGEXMATCH Formula Examples

Let’s see another example.

The following REGEXMATCH function checks whether the input strings contain numbers. This can be done by using “\d” as the regular expression which stands for digits.

The function is:

=REGEXMATCH(A3,"\d")

Adding “\d” can be a useful way to see what page titles pulled from the SERPs contain numbers:

The REGEXMATCH function is also helpful when you want to filter your data set and in any spreadsheet formulas that need logical tests. It allows you to apply more specific filter conditions.

Let’s create a FILTER function that uses the REGEXMATCH result as its logical expression.

Since the FILTER function works with arrays, the single-cell reference in REGEXMATCH should be changed to an array reference that contains the whole range to be filtered.

The function will look like this:

=FILTER(A2:A8,REGEXMATCH(E3:E8,"\d"))

And as a result, the FILTER function outputs the titles whose REGEXMATCH test returned TRUE, (aka the ones that contain digits).

The REGEXMATCH function only works with text (not numbers) as input and returns a logical value (TRUE or FALSE) as output.

If you don’t mind formatting the numbers to texts permanently, select Format > Number > Plain text in the menu.

But if you don’t want to change the content of the original cells, you can convert them to text with the TEXT function before using it in your REGEXMATCH function.

In this case, the first argument of the REGEXMATCH function should be a TEXT function:

TEXT(A2, “##”)

This will convert the content of cell A2 to a textual value in a two-digit format.

This allows you to treat these numbers as text within the REGEXMATCH formula and look for matches in these converted numbers.

The function that does this is:

=REGEXMATCH(TEXT(A2,"##"),"93")

Summary

REGEXMATCH has made my life a lot easier.

Sure, sometimes I’ve ended up with super long formulas that make my eyes slowly melt out of my head trying to decipher them, but hey, at least my keywords have been categorised.

Once you’ve got the hang of it, have a play around with REGEX.  It’s very useful and surprisingly easy to learn.

You should check out the keyword research Google Sheets Templates in the directory for inspiration of what you can do with this formula.