RegexMatch on Google Sheets: Guide 2023

Regex are an indispensable tool on Google Sheet that will allow you to drastically increase your productivity in addition to allowing you to identify and clean your files

in this article, we will discuss the function
=regexmatch()

We will first explain what RegexMatch is for on Google Sheet, then we will see how to use RegexMatch and finally we will see some very nice use cases

If you want to know more about the Regex types available on Google Sheet, go to this article

RegexMatch on Google Sheet: What’s it for?

Concretely, Regex are used to do semantic analysis. It is a “semi” programming language that allows to identify patterns in an object.

There are a lot of technical terms in this sentence so I’ll clarify.
By semantic analysis, I mean analyzing a text, a sequence of numbers, characters and finding common points in these elements. These “elements” are what I call objects above.

Objects can be of a multitude of types

• texts
• letters
• numbers
• characters
• emojis
• …

Any character or sequence of characters = 1 object (for example a Google Sheet cell). Regex will then allow us to search in these objects in an automated and fast way and to find a sequence of characters we are looking for and then do something with it (you define what you are looking for)

In the case of RegexMatch, what the formula will do is to search in the objects and find a sequence of characters that you will ask it to finally tell you if this sequence of characters exists in the object / objects that you send it

Let’s take an example

I have 300 “objects” which are google sheet cells filled with data say first names. I’m looking to see if the first name has +4 letters or that the first names are all capitalized in the 1st letter. With a very simple Regex and the RegexMatch formula I can know instantly if it is the case or not. That’s what RegexMatch is for. to tell you if YES or NO it finds what you ask it.

Example of use case (simple)

Well the example, is not the most representative of what can allow you to do the regexMatch of Google Sheet but you will see much more interesting cases below!

RegexMatch on Google Sheet : Explanation of the formula

Let’s analyze the google Sheet RegexMatch formula

According to the official documentation of Google Sheet here is what we have:

REGEXMATCH(text, regular_expression)
text – The text to be tested against the regular expression.
regular_expression
– The regular expression to test the text against.

Google documentation

First of all you have to know that every formula on Google Sheet always starts with an “=”

By putting “= ” you explain to Google that you are going to perform an operation or a formula

Be careful, depending on the language of your google account, the “,” can be “;” so if it doesn’t work try to put “;” instead of “,”.

Now let’s get to the heart of the matter of RegexMatch.
The RegexMatch formula takes 2 elements that you have to provide.

1. The object, understand by this, the cell that it must analyze
2. The Regex, understand by it what it must look for in this cell.

In our example it would give in a language understandable for a non-tech human :
=Regexmatch(my cell;tell me TRUE if in my cell you find only 4 letters)

Obviously, the Regex language is a bit more complex but not so complicated actually if you take the time to understand. If you don’t have any tech knowledge, count 1 day to master Regex. If you have some tech knowledge, in 2-4h you have understood and could apply Regex in all your routines.

If you want to learn Regex, here are 2 very useful resources:

• https://formation.deux.io/cours/regex
• https://regex101.com/

With these 2 elements you will become a real pro 🙂

Let’s go to the use case!

How to use RegexMatch for better prospecting?

It is not uncommon to use Google Sheet as a prospecting database. You use it to list your customers, your prospects, your potential targets to contact.

I will explain you how to build and clean a database with

• Derrick App
• RegexMatch from Google Sheet
• Linkedin sales Navigator : you find a hack to get it for free here

All 100% free.

Do you want to try it? Here we go.

Install derrick app

• Download derrick App on Google Sheet
• Open a Google Sheet
• Go to “Extension” in the Google Sheet navigation bar
• Open derrick.

Do your search on LinkedIn SalesNavigator

• Go to Sales Navigator
• Do a search to target the leads you are interested in, you can use boolean operators to refine your results.

Export your leads to Google Sheet

Very easy, just copy and paste your search URL into derrick’s “Lead Import” feature

⚠️ Remember to add your li_at so you can perform this action. To do so here are 2 options

1. Download this chrome extension that will find your li_at for you
2. Read this article

Here is a video to help you

Using RegexMatch in your Google Sheet

At this point you should have your lead list in a Google Sheet, well done

It’s time to use the power of Google Sheet with RegexMatch!

We will try to filter our list to keep only those who are “CEO” or “Founder” or “President”.

Option 1: Set a filter by clicking on

Then select in the “title” column the “CEO” or “Founder” or “President”.

It’s super fast, the disadvantage is when doing so you will not have profiles that will note “CEO” + something else yet they are relevant example Michel here will not be in your filter because it does not exactly meet your filter (find the “CEO” with only CEO in the title)

Option 2: you use Regexmatch on Google Sheet.
Youindicate that you want RegexMatch to search in the cell if it finds “CEO” or “Founder” or “President”.

In Regex, it will look like this

=regexmatch(MyCell;"(CEO|President|Founder)")

Then stretch the formula to your whole column and this is what happens

You see, nothing could be simpler, you can now filter your RegexMatch column to keep only those that are good (CEO|President|Founder) by clicking only on the “TRUE” ones

• the “|” means OR in Regex
• the () means in this set
• so “(that|or that|or else that)”.

The difference here is that RegexMatch will not look if you have expressly CEO but will tell you YES it is well noted CEO in this box but it can also have noted other things

I hope this example has helped you understand how RegexMatch can be used for prospecting. Now imagine being able to do this to

• identify if a lead has more than x years of experience in his field
• identify if a lead is new to the company
• identify if a lead has a particular skill in his profile
• identify if a lead …

All this information Derrick will give you.
With a little practice on Regex, you will be able to create “laser” prospecting lists!

Using RegexMatch in your Google Sheet

Finding the best people for your business is becoming more and more complicated as the market gets tighter.
With the help of derrick and RegexMatch in Google Sheet, you can find good profiles according to your criteria and target students fresh out of school!

How to do it ?

It’s simple, we’ll repeat the same steps to find our profiles with Linkedin sales Navigator & derrick and then enrich your data

Then, filter your lists from RegexMatch to for example

• Recover their professional experience to find students who have done internships / work-study
• retrieve students who have finished their studies
• ….

Then you just have to realize a top sourcing strategy!