REGEXMATCH function in Google Sheets

REGEXMATCH tests whether a text matches a pattern and returns TRUE or FALSE.

A regular expression is a pattern that describes a shape of text: "starts with two capital letters", "contains a digit", "ends in .com". REGEXMATCH asks whether a cell fits the pattern. It turns checks that would need a tangle of LEFT, MID and LEN into one short formula.

Syntax

=REGEXMATCH(text, regular_expression)

Argument	What it is
`text`	The text to test. It must be text, not a number.
`regular_expression`	The pattern, in quotation marks.

Examples

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=REGEXMATCH(A4,"gill")
Result: TRUE
Plain letters match themselves anywhere in the text: "meena gill" contains gill.
=REGEXMATCH(A3,"kumar")
Result: FALSE
Patterns are case sensitive. The cell holds RAVI KUMAR in capitals.
=REGEXMATCH(A3,"(?i)kumar")
Result: TRUE
Starting the pattern with (?i) makes it ignore case.
=REGEXMATCH(B2,"@example\.com$")
Result: TRUE
The dollar sign means "at the end". A dot is special, so a real dot is written with a backslash.
=REGEXMATCH(C2,"^[A-Z]{2}-\d{2}-\d{4}$")
Result: TRUE
A whole-cell check of a code's shape: two capital letters, dash, two digits, dash, four digits.
=REGEXMATCH(D5,"^\d+$")
Result: FALSE
Only digits from start to end? "12 kg" fails.
=REGEXMATCH(B3,"\.(com|org)$")
Result: TRUE
The bar means "or": ends in .com or .org.

How it works

REGEXMATCH returns TRUE if the pattern is found anywhere in the text, and FALSE otherwise. To insist that the whole cell fits, anchor the pattern with ^ at the start and $ at the end.

Sheets uses the RE2 flavour of regular expressions.

The building blocks

Pattern	Matches
`abc`	exactly those letters
`.`	any single character
`\d`	a digit
`\w`	a letter, digit or underscore
`\s`	a space or tab
`[abc]`	any one of a, b, c
`[A-Z]`	any capital letter
`[^0-9]`	any character that is not a digit
`^`	the start of the text
`$`	the end of the text
`a\|b`	a or b
`( )`	a group

And how many times:

Pattern	Means
`*`	none or more
`+`	one or more
`?`	none or one
`{4}`	exactly four
`{2,5}`	two to five

A character with a special meaning, such as a dot, a plus or a bracket, needs a backslash in front to be taken literally: \.

Case

Patterns distinguish capital and small letters. Put (?i) at the very start to ignore case, as in the third example.

Where it earns its keep

In FILTER. =FILTER(A2:F, REGEXMATCH(D2:D, "(?i)book|pen")) shows rows whose product contains either word. FILTER has no "contains" of its own.

In IF. =IF(REGEXMATCH(A2, "^\d{6}$"), "OK", "Check PIN") flags PIN codes that are not six digits.

In data validation. Under Data, Data validation, choose "Custom formula is" and give a rule such as =REGEXMATCH(A2, "^[A-Z]{5}\d{4}[A-Z]$") to accept only a certain shape of code.

In conditional formatting, with the same kind of formula, to colour the cells that do or do not fit.

In COUNTIF-style counts. =SUM(ARRAYFORMULA(--REGEXMATCH(A2:A100, "pattern"))) counts the cells that match.

Only text

REGEXMATCH works on text. Given a number, it returns #VALUE!. Convert first: =REGEXMATCH(TO_TEXT(A2), "^\d{10}$"), or A2&"".

Keep patterns honest

A pattern can check the shape of a value, not whether it is real. A text can look like a phone number or an email address and belong to nobody. Use patterns to catch typing slips, not as proof.

Common mistakes

Forgetting ^ and $, so that a pattern meant for the whole cell matches a part of it.
An unescaped dot, which matches any character.
Forgetting that case matters.
Giving a number or an empty cell.
Using wildcards from COUNTIF. In a regular expression, * means "repeat the previous thing". "Anything" is .*

In Excel

Excel 2021 and earlier have no regular-expression functions. Recent Microsoft 365 has added REGEXTEST for the same job. The usual substitute in older Excel is ISNUMBER(SEARCH(...)) with wildcards.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.