How to filter rows that contain specific text in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)))Combine FILTER with SEARCH: =FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10))) returns every row whose product name contains the text in G1.
- Result on the example
- a 4-row list that spills from F3 in
F3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F3. Change any input and the result updates, or edit the formula to experiment.
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".
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)))Result{4 rows × 4 cols} "P-101", "Desk lamp", "Lighting", 39.99, …
A2:D109 rows × 4 cols: "P-101", "Desk lamp", "Lighting", $39.99, …A range: 9 rows × 4 columns.
G1"desk"The value in G1.
B2:B109 rows × 1 col: "Desk lamp", "Office chair", "Standing desk", "Monitor arm", …A range: 9 cells in one column.
SEARCH(G1,B2:B10){9 rows × 1 col} 1, #VALUE!, 10, #VALUE!, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
G1"desk" - within_text
B2:B109 rows × 1 col: "Desk lamp", "Office chair", "Standing desk", "Monitor arm", …
- find_text
ISNUMBER(SEARCH(G1,B2:B10)){9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(G1,B2:B10){9 rows × 1 col} 1, #VALUE!, 10, #VALUE!, …
- value
FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10))){4 rows × 4 cols} "P-101", "Desk lamp", "Lighting", 39.99, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:D109 rows × 4 cols: "P-101", "Desk lamp", "Lighting", $39.99, … - condition1
ISNUMBER(SEARCH(G1,B2:B10)){9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10))). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:D10,ISNUMBER(FIND(G1,B2:B10))) | spills 1 × 4Different result | 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))Google Sheets | spills 4 × 4 | 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.
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 that contain specific text
F3, return every product row whose name contains the text in G1, in any capitalization.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))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.