How to Use the REGEXMATCH Function in Google Sheets
=REGEXMATCH(text, regular_expression). It works only with text, not numbers.
What REGEXMATCH does
Where REGEXEXTRACT returns the matching piece, REGEXMATCH only answers yes or no. That makes it a natural partner for IF, filters and conditional formatting: "does this cell look like an email?", "does this product code start with AB?"
REGEXMATCH syntax
=REGEXMATCH(text, regular_expression)
Per Google's help page, the first argument is the text to test and the second is the pattern. The result is a logical value, TRUE or FALSE. The function works only with text as input. To test a number, convert it with TEXT first. Sheets uses the RE2 syntax, except for Unicode character class matching. The page does not say whether matching is case sensitive, so test your own data when case matters.
A step-by-step example
Cells A2 and A3 hold two entries. One looks like an email address and one does not.
| A | |
|---|---|
| 1 | Entry |
| 2 | ann@example.com |
| 3 | bob.example.com |
=REGEXMATCH(A2, "@")
This returns TRUE, because the text contains an @. The same formula on A3 returns FALSE. A pattern matches anywhere inside the text unless you anchor it.
Use it inside IF
Wrap it in IF to turn TRUE and FALSE into labels:
=IF(REGEXMATCH(A3, "@"), "Valid", "Invalid")
This returns Invalid, because A3 has no @.
Common mistakes
- Passing a number. Convert it to text with TEXT first.
- Forgetting the quotation marks around the pattern.
- Expecting a whole-text match. A pattern like
"@"matches anywhere. To require the whole text, anchor the pattern. - Special characters. A dot means any character in a pattern, so a literal dot needs a backslash before it.
Try it yourself
Use the entries above (A2 and A3). Type your formula into the box, starting with =, then press Check.
Exercise 1: Has an @?
Write a formula that tests whether the text in A2 contains an @.
Exercise 2: Valid or invalid
Write a formula that shows Valid if A3 contains an @ and Invalid if it does not.
What to learn next
- REGEXEXTRACT: pull text out with a pattern.
- REGEXREPLACE: replace text by pattern.
- IF: run a test and return one of two results.
Syntax and rules in this guide were checked against Google's REGEXMATCH help page. See all guides.