Google Sheets REGEX functions are pure text functions. Among the three REGEX functions, I’ve already explained the use of REGEXEXTRACT. Other two REGEX functions are REGEXMATCH and REGEXREPLACE. Here we can learn how to use REGEXMATCH function in Google Sheets.
Learning REGEXMATCH is important as you can use this in combined or nested form with functions like FILTER for logical tests. REGEXMATCH returns Boolean TRUE or FALSE.
Many users skip the REGEX functions due to the lack of tutorials or guidance. Because this function contains RE2 regular expressions, which may be new to many and also seems complicated. With this tutorial, I wish I could shed some light on the usage of REGEX formula.
Google Sheets REGEXMATCH Function
Here I’m going to provide you a good exposure to the use of REGEXMATCH with the help of examples. Below are the purpose and syntax.
Purpose of REGEXMATCH Formula
The purpose of this function is to find whether a piece of text matches a regular expression. When you go through the examples below, you can understand it clearly.
Syntax
REGEXMATCH(text, regular_expression)
text is the ‘text’ to be tested against the regular expression.
regular_expression – The regular expression to test the text against.
Example to REGEXMATCH Function
1. Regexmatch to match any plain text in a word, phrase or sentence.
Text in Cell A2:Â Info Inspired 2017, India
Formula:
=REGEXMATCH(A2, "Info Inspired")
Result: TRUE
Text in Cell A3:Â InfoInspired 2017, India
Formula:
=REGEXMATCH(A3, "Info Inspired")
Result: FALSE
Text in Cell A4:Â Info inspired 2017, India
Formula:
=REGEXMATCH(A3, "Info Inspired")
Result: FALSE
2. Regexmatch to match the presence of either of the single character in a word, phrase or sentence, Similar to “this or that”.
Text in Cell A4: Info Inspired 2017, India
Formula:
=REGEXMATCH(A4, "y|o")
Result: TRUE
Formula:
=REGEXMATCH(A4, "b|z")
Result: FALSE
3. Regexmatch to match either of the words in a sentence.
Text in Cell A8: Info Inspired 2017, India
Formula:
=REGEXMATCH(A8, "blog|India")
Result: TRUE
4. Regexmatch to match whether the text contains a specific number of characters.
Text in Cell A9: Info Inspired 2017, India
Formula:
=REGEXMATCH(A9, "...")
Result: TRUE
Another variation of the above Match.
=REGEXMATCH(A9, "Ins...")
Text in Cell A9: Cloud
Formula:
=REGEXMATCH(A9, ".......")
Result: FALSE
5. Regexmatch to match the presence of any number of decimal digits in the text.
Text in Cell A11: Info Inspired 2017, India
Formula:
=REGEXMATCH(A11, "(\d)")
Result: TRUE
6. Regexmatch to match the presence of any specific decimal digits in the text.
Text in Cell A12: ABC6
Formula:
=REGEXMATCH(A12, "[5-9]")
Result: TRUE
7. Regexmatch to match any of the specific characters.
Text in Cell A13: Info Inspired 2017, India
Formula:
=REGEXMATCH(A13, "[dxy]")
Result: TRUE
8. Regexmatch to match the presence of any small case letters.
Text in Cell A14: D
Formula:
=REGEXMATCH(A14, "[a-c]")
Result: FALSE
9. Regexmatch to match the presence of any UPPER case letters.
Text in Cell A15: D
Formula:
=REGEXMATCH(A15, "[A-Z]")
Result: TRUE
10. Regexmatch to match any content between question marks.
Text in Cell A16:Â Can you find this ?Content? between question marks
Formula:
=REGEXMATCH(A16, "\?([A-Za-z]+)\?")
Result: TRUE
11. Regexmatch to match any content between brackets.
Text in Cell A17: HI (Content) between question marks
Formula:
=REGEXMATCH(A17, "\(([A-Za-z]+)\)")
Result: TRUE
12. Regexmatch to match any email address.
Text in Cell A18: email me <info@infoinspired.com>
Formula:
=REGEXMATCH(A18, "<(.+)>")
Result: TRUE
13. Regexmatch to Exact Match a String.
Text in Cell A19: student 19
Formula:
REGEXMATCH(A19,"^student 1$")
Result: FALSE
If you change the string to student 1, then the formula would return TRUE.
Related: Regexmatch in Filter Criteria in Google Sheet
Conclusion:
Hope you could learn the use of REGEXMATCH in Google Sheets. You can feel free to check our ultimate Google Sheets Function guide to learn all Google Sheets trendy functions.
I have tried the examples here in relation to a workbook I am using and they all return parse errors.
Any suggestions as to where to check appreciated.
Hi, CJ,
It may due to LOCALE settings. Replace the comma in your formula with a semicolon.
I want to match inside query the expression:
6.1)
How can I do that, I’ve tried:
matches '6.1)'
– not workingmatches '\6.1\)'
– not workingmatches '\6.1)\)'
– not workingPlease help
Hi, Jofre Navarrete,
I assume you were talking about the Matches Regular Expression Match in Google Sheets Query.
In that case the formula would be as below.
=query(C1:D,"Select D where D matches '6.1\)'")
You only need to escape the closing bracket.
Thank You Prashanth