REGEXMATCH tests whether a piece of text contains a pattern and returns TRUE or FALSE. It makes IF, FILTER and conditional formatting rules far more powerful than a plain "equals" or "contains" check, for example "starts with INV followed by digits" or "contains any of these five words".
Syntax
=REGEXMATCH(text, regular_expression)
text: The text to test.regular_expression: The pattern, as text in quotes. Google Sheets uses the RE2 syntax.
REGEXMATCH returns TRUE if the pattern matches anywhere in the text. To require that the whole text matches, anchor the pattern with ^ at the start and $ at the end.
Basic examples
=REGEXMATCH("Order #4521", "\d+")
Returns TRUE: the text contains digits.
=REGEXMATCH(A2, "^INV-\d{4}$")
Returns TRUE only if the cell is exactly INV- followed by four digits. The anchors mean "INV-12345" or " INV-1234" would fail.
=REGEXMATCH(A2, "(?i)urgent")
Case-insensitive match. By default matching is case-sensitive.
Regex building blocks
\ddigit,\wletter/digit/underscore,\swhitespace (uppercase versions mean the opposite).any character,+one or more,*zero or more,?optional[abc]one of a, b or c.[a-z]a lowercase letter.[^0-9]anything but a digit^start of text,$end of texta|ba or b{3}exactly three,{2,4}two to four(?i)at the start: ignore case
To match a literal special character such as . or (, put a backslash in front of it.
Practical patterns
Contains any of several words
=REGEXMATCH(A2, "(?i)refund|chargeback|dispute")
One formula replaces a chain of OR(SEARCH(...)) tests.
Contains any word from a list in cells
=REGEXMATCH(A2, TEXTJOIN("|", TRUE, $F$2:$F$10))
Joins the list into word1|word2|word3, so you can maintain the keywords in cells.
Validate an email address
=REGEXMATCH(A2, "^[\w.+-]+@[\w-]+\.[\w.-]+$")
This catches obvious typos. It is a sanity check, not a full email validator.
Check a format
=REGEXMATCH(TO_TEXT(A2), "^\d{5}$")
A five-digit ZIP code. Use TO_TEXT because REGEXMATCH expects text and a number may lose leading zeros anyway.
=REGEXMATCH(A2, "^\+?\d[\d\s().-]{7,}$")
A loose phone number check.
Filter rows by a pattern
=FILTER(A2:C100, REGEXMATCH(A2:A100, "^INV-"))
REGEXMATCH works on a whole range inside FILTER, without needing ARRAYFORMULA.
Count matches
=SUMPRODUCT(--REGEXMATCH(A2:A100, "(?i)error"))
Counts how many cells contain error.
Conditional formatting
Use a custom formula rule such as =REGEXMATCH($A2, "(?i)overdue") to highlight whole rows.
Mark each row
=ARRAYFORMULA(IF(REGEXMATCH(A2:A, "(?i)vip"), "VIP", "Standard"))
Common errors
#VALUE!: "Invalid regular expression". Unbalanced parentheses or brackets, or RE2-unsupported features such as lookaheads(?=...)and backreferences.- Always
FALSEunexpectedly. Case sensitivity (add(?i)), leading or trailing spaces (useTRIM), or unescaped special characters such as.. - Always
TRUEunexpectedly. The pattern is too loose and is not anchored."\d+"matches any text with a digit. Add^and$. - Numbers behave oddly. Numbers are converted to text, so formatting like
1,000is lost. Convert deliberately withTO_TEXT. - Empty pattern.
REGEXMATCH(A2, "")matches everything.
REGEXMATCH vs. SEARCH vs. FIND vs. CONTAINS-style checks
SEARCH and FIND give a position and need ISNUMBER to become a yes/no test. REGEXMATCH returns the yes/no directly and handles alternatives, anchors and character classes. For a simple "contains this exact word", ISNUMBER(SEARCH("word", A2)) is enough. Reach for REGEXMATCH when the rule has any shape or variation.
Frequently asked questions
Is REGEXMATCH case-sensitive? Yes by default. Use (?i) at the start of the pattern.
Does it match the whole text? Only if you anchor it with ^ and $. Otherwise any part can match.
Can it check a range? Yes, inside FILTER or ARRAYFORMULA.
Which regex flavor? RE2: no lookaheads, lookbehinds or backreferences in the pattern.
Related Functions
REGEXEXTRACT: Extract matching text.REGEXREPLACE: Replace matching text.FILTER: Return rows that meet a condition.IF: Return different values based on a condition.SPLIT: Split text at a delimiter.