The REGEXMATCH Function in Google Sheets is useful if you want to know if a piece of text 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.
Table of Contents
Let’s take an example:
You run a T-shirt printing company and you would like to know what colors are more popular for your upcoming production. So you send an email containing a Google Sheet to the existing customer to ask what their favorite colors are.
To check if “Blue” is a popular color choice, you can use the REGEXMATCH
Function to identify which customers wrote “Blue” as their favorite color. Here is how it looks like on Google Sheets:
As shown above, simply input the attribute (regular expression) that you are looking for into the function and it will show “TRUE” if the customer’s favorite color is “Blue” or “FALSE” if the customer did not mention the color “Blue”.
You can even take it up a notch and input several colors into the function by using the |
to separate each attribute. The bar character |
is used as an Or operator in the REGEXMATCH
Function.
Now you would not miss out on the most desirable colors to produce to maximize profits in your next T-shirt release! 🙌
The Anatomy of the REGEXMATCH Function
The way we write the REGEXMATCH Function is:
=REGEXMATCH(text, regular_expression)
Let us help you understand the context of the function:
- The equal sign
=
is how we start any function in Google Sheets. REGEXMATCH()
is our function. We need to add two attributes, namely thetext
andregular_expression
, to make it work correctly.- The
text
is the cell where you want to match aregular_expression
to. In our example above, it will be A2. - The
regular_expression
is the word we want to match to the text.
Here is a more detailed understanding of what a regular_expression
means. A regular expression is a text pattern. It is a sequence of characters, including letters, symbols, and numbers combined together.
Metacharacters:
A regular expression is often characterized by certain special symbols, also known as “metacharacters” like the \
used in our example above.
Here are some common metacharacters that the REGEXMATCH
Function supports:
Metacharacter |
Representation |
^ | The beginning of the string. |
$ | The end of the string. |
. | A single character. |
| | The Or operator. |
? | One allowed, but it is optional. |
+ | At least one required; additional are optional. |
\ | General escape character. |
[] | This holds a set of characters and represent any one of the characters listed inside it. |
[^] | This holds a set of characters and represent any one of the characters not listed inside it. |
() | This holds a group of metacharacters inside it and represents that sequence of characters. |
Apply these metacharacters in other regular expression functions as well!
Let’s take note that:
- This function only works with text as an input. If numbers are used as input, convert them to text using the
TEXT
function. - The
REGEXMATCH
function is case-sensitive. Therefore, you will need to specify the correct case inside the regular expression.
A Real Life Example of Using REGEXMATCH Function
Let’s use a real-life situation to utilize the REGEXMATCH
Function and combine all the components mentioned so far in practical use and show you how powerful this function can be!
Oh no, there are so many characters in the formula. What’s going on? 😵
No worries, let us guide you step-by-step!
In this example, we use the REGEXMATCH
Function to determine which email addresses are valid and which are not.
This example can be applied to several business settings, especially to check if the customer information is valid during their registration process.
How to Use REGEXMATCH Function in Google Sheets
- Simply click on the cell that you want to write down your function at. In this example, it will be C2.
- Begin your function with an equal sign
=
, then followed by the name of the function,REGEXMATCH
, then an open parenthesis(
.
- We will then select cell B2, as this is the text we want to match our regular expression to. Furthermore, we need to add a comma
,
to separate the text from our next attribute, ourregular_expression
.
- Next, enclosed by a quote-unquote symbol
""
, type in the regular expressions we want to look for. For an email address, there are several components that need to be present in order for it to be a valid email address. We have broken down a few components of an email address to let you understand what we mean:
For example: [email protected]
- Let’s figure out the regular expression for the beginning part of the email. We will continue the formula with
[]
to indicate that any one of the characters inserted inside the array would be “TRUE”. Possible characters that are used in the beginning of an email address are lower and upper case alphabets, numbers, dots.
and underscores_
.
So as shown in the display above, we will include all of the possible characters into the array making the formula look like this:
=REGEXMATCH(B2, “[a-zA-Z_.1-9]”)
- After the beginning of the email address, there would be an at sign
@
. To continue the formula, we will need to add a plus sign+
then continue to add a@
.
- Behind the
@
will be a domain name (for example, gmail, outlook, yahoo, etc.). Hence like before, we will need to add another+
to indicate another optional regular expression.
So just like the beginning of an email address, the domain name will contain characters such as lower and upper case alphabets, making the formula look like this:
=REGEXMATCH(B2, “[a-zA-Z_.1-9][email protected]+[a-zA-Z]”)
- To finish off a domain name, it will usually end with “.com” or “.edu”, etc. So to continue, we will add a
+
then\.
to indicate the dot sign. The\
is used to indicate that in this context, it is not to be used as a metacharacter but just a character dot sign.
After the.
it would usually contain lower or upper case letters.
- Lastly, some email addresses would end with a “.co”. Similar to Step 8, add a
\.
and'[a-zA-Z]'
into our formula. To include the “some” factor into the formula, we will add a bracket sign()
followed by a question mark sign?
to indicate that the group of characters are optional. So even if this group character does not exist in some of the email addresses, the email address is still valid and shows a “TRUE”.
Our final formula would look like this:
=REGEXMATCH(B2, “[a-zA-Z_.1-9][email protected]+[a-zA-Z]+\.[a-zA-Z]+(\.[a-zA-Z])?”)
- After the following steps, your input should look like this.
- So to give you a summary of how the formula was formed, here is a visual guide:
In just ten simple steps, you are done! Yay.💃
You may make a copy of the spreadsheet using the link attached below and try it for yourself:
Another way you can use REGEXMATCH
function is to match it with other functions like ARRAY
, IF
and LEN
.
Combining these functions together can help you check if multiple values are available within an array in Google Sheet. Click here to learn more!
You can now use the REGEXMATCH
function together with many more Google Sheets formulas to create even more powerful formulas.