# How to filter rows with REGEXMATCH in Google Sheets

Use REGEXMATCH as the FILTER condition: `=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))` keeps the rows whose email matches the pattern in F1.

**Google Sheets:** `=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))`

**Excel:** Microsoft 365 has REGEXTEST for the same test: `=FILTER(A2:B10,REGEXTEST(B2:B10,F1))`. Other versions can test simpler patterns with ISNUMBER and SEARCH.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Name | Email |  |  | Pattern | @(acme\|globex)\.com$ |
| Ana Ruiz | ana.ruiz@acme.com |  |  |  |  |
| Raj Patel | raj@initech.com |  |  |  |  |
| Lee Chen | lee.chen@globex.com |  |  |  |  |
| Kim Ortiz | kim@acme.co |  |  |  |  |
| Mo Diaz | mo.diaz@globex.com |  |  |  |  |
| Sam Cole | sam@umbrella.org |  |  |  |  |
| Eve Lund | eve.lund@acme.com |  |  |  |  |
| Noor Ali | noor@acme.com.mx |  |  |  |  |
| Ivy Shah | ivy@hooli.com |  |  |  |  |

Formula in E3: `=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))`

Result (spills 4 × 2): Ana Ruiz, ana.ruiz@acme.com; Lee Chen, lee.chen@globex.com; Mo Diaz, mo.diaz@globex.com; Eve Lund, eve.lund@acme.com

## 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.

## Other ways

- `=FILTER(A2:B10,REGEXMATCH(B2:B10,"acme|globex"))`: 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)))`: Wrap the test in NOT to get everyone else. (The five contacts at other domains.)

## Common mistakes

- An unescaped dot matches any character: `acme.com` also 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")`.

## 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'`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/filter-rows-with-regexmatch