How to filter rows with REGEXMATCH in Google Sheets
Google Sheets formula
=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))Use REGEXMATCH as the FILTER condition: =FILTER(A2:B10,REGEXMATCH(B2:B10,F1)) keeps the rows whose email matches the pattern in F1.
- Result on the example
- a 4-row list that spills from E3 in
E3 - Excel
- See below
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER evaluates REGEXMATCH for every email and keeps the rows where it is TRUE. The pattern says: an @, then acme or globex, then .com at the very end ($). That excludes acme.co and acme.com.mx, which a simple "contains acme" test would let through. The \. matches a real dot.
=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))Result{4 rows × 2 cols} "Ana Ruiz", "ana.ruiz@acme.com", "Lee Chen", "lee.chen@globex.com", …
A2:B109 rows × 2 cols: "Ana Ruiz", "ana.ruiz@acme.com", "Raj Patel", "raj@initech.com", …A range: 9 rows × 2 columns.
B2:B109 rows × 1 col: "ana.ruiz@acme.com", "raj@initech.com", "lee.chen@globex.com", "kim@acme.co", …A range: 9 cells in one column.
F1"@(acme|globex)\.com$"The value in F1.
REGEXMATCH(B2:B10,F1){9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …REGEXMATCH: TRUE if the text contains a match for the pattern.- text
B2:B109 rows × 1 col: "ana.ruiz@acme.com", "raj@initech.com", "lee.chen@globex.com", "kim@acme.co", … - regular_expression
F1"@(acme|globex)\.com$"
- text
FILTER(A2:B10,REGEXMATCH(B2:B10,F1)){4 rows × 2 cols} "Ana Ruiz", "ana.ruiz@acme.com", "Lee Chen", "lee.chen@globex.com", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:B109 rows × 2 cols: "Ana Ruiz", "ana.ruiz@acme.com", "Raj Patel", "raj@initech.com", … - condition1
REGEXMATCH(B2:B10,F1){9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
- range
In Excel
=FILTER(A2:B10,REGEXTEST(B2:B10,F1)). Other versions can test simpler patterns with ISNUMBER and SEARCH.Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:B10,REGEXMATCH(B2:B10,"acme|globex")) | spills 6 × 2Different result | A looser test: the words anywhere in the email. Also returns kim@acme.co and noor@acme.com.mx. |
| =FILTER(A2:B10,NOT(REGEXMATCH(B2:B10,F1))) | spills 5 × 2Different result | Wrap the test in NOT to get everyone else. The five contacts at other domains. |
Common mistakes
- An unescaped dot matches any character:
acme.comalso matches "acmexcom". Escape it as\.. - Without the
$anchor, the pattern matches anywhere, so acme.com.mx passes. - If nothing matches, FILTER returns #N/A. Wrap it:
=IFNA(FILTER(…),"None").
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Filter rows with REGEXMATCH
E3, return the name and email of every contact whose email matches the regular expression in F1.Questions
How do I make the match ignore capitals?
Start the pattern with (?i).
Can QUERY do the same?
Yes, with matches, but it must match the whole value: where B matches '.*@(acme|globex)\.com'.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.