# How to filter rows that contain specific text in Excel

Combine FILTER with SEARCH: `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)))` returns every row whose product name contains the text in `G1`.

**Excel:** `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Category | Price |  | Contains | desk |
| P-101 | Desk lamp | Lighting | 39.99 |  |  |  |
| P-102 | Office chair | Seating | 249 |  |  |  |
| P-103 | Standing desk | Furniture | 449 |  |  |  |
| P-104 | Monitor arm | Accessories | 89.5 |  |  |  |
| P-105 | Desk organizer | Accessories | 24.95 |  |  |  |
| P-106 | Webcam | Electronics | 59 |  |  |  |
| P-107 | Desk mat | Accessories | 19.5 |  |  |  |
| P-108 | Laptop stand | Accessories | 45 |  |  |  |
| P-109 | Floor lamp | Lighting | 79 |  |  |  |

Formula in F3: `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)))`

Result (spills 4 × 4): P-101, Desk lamp, Lighting, $39.99; P-103, Standing desk, Furniture, $449.00; P-105, Desk organizer, Accessories, $24.95; P-107, Desk mat, Accessories, $19.50

## How it works

SEARCH returns the position where the text starts, or #VALUE! when it isn't there. ISNUMBER turns that into TRUE or FALSE for each row, which is exactly what FILTER needs. SEARCH ignores case, so "desk" finds "Desk lamp" and "Standing desk".

## Other ways

- `=FILTER(A2:D10,ISNUMBER(FIND(G1,B2:B10)))`: FIND is the case-sensitive version: "desk" no longer matches "Desk lamp". (Only Standing desk, the one name with a lowercase "desk".)
- `=FILTER(A2:D10,REGEXMATCH(B2:B10,"(?i)"&G1))`: In Google Sheets, REGEXMATCH can test several words at once, such as "desk|lamp".

## Common mistakes

- Wildcards don't work in FILTER: `B2:B10="*desk*"` looks for the asterisks literally. Use SEARCH.
- An empty search cell matches every row, because SEARCH finds empty text at position 1. Add `*(G1<>"")` if that matters.
- SEARCH finds the text inside longer words: "arm" also matches "Warmer". Search for " arm" with a space, or a whole word, when that's a problem.

## Questions

**How do I filter rows that do not contain the text?**

Flip the test with NOT: `=FILTER(A2:D10,NOT(ISNUMBER(SEARCH(G1,B2:B10))))`.

**How do I search two columns at once?**

Join them before searching: `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10&" "&C2:C10)))`.

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-that-contain-text