FILTER Rows That Contain Text in Excel

To filter rows that contain text in Excel, the FILTER function needs a little help from SEARCH and ISNUMBER. =FILTER(A2:C8,ISNUMBER(SEARCH("desk",A2:A8))) returns every product with "desk" anywhere in its name. This guide explains how the pattern works, then covers case-sensitive matches, several keywords, "does not contain", and a reusable search box.

Why can't FILTER use wildcards?#

FILTER keeps rows where a test is TRUE, and an equals test only matches whole cells. So A2:A8="desk" finds cells that are exactly "desk", and A2:A8="*desk*" looks for the actual asterisks. Wildcards work in COUNTIF and XLOOKUP, but not in an = comparison.

The fix is SEARCH, which looks for text inside a cell. If you've used wildcards in criteria before, the guide to IF with text shows the same SEARCH idea in IF formulas.

All examples use this product list in A1:C8:

How to filter rows that contain text#

Combine three functions, from the inside out:

=FILTER(A2:C8,ISNUMBER(SEARCH("desk",A2:A8)))

1. SEARCH("desk",A2:A8) looks for "desk" in each product name. It returns the position where it's found, such as 10 for "Standing desk" and 1 for "Desk lamp", or #VALUE! where it isn't.
2. ISNUMBER(...) turns each result into TRUE (a number, so found) or FALSE (an error, so not found).
3. FILTER(A2:C8,...) keeps the rows where the test is TRUE.

The result is three rows: Standing desk, Desk lamp and Desk organiser. SEARCH ignores case, so "Desk" and "desk" both match.

FILTER needs Excel 2021, Excel 2024 or Microsoft 365. The results spill into the cells below and to the right, so keep that area empty. The FILTER function guide and the SEARCH function guide cover each function on its own.

How to make the match case-sensitive#

Swap SEARCH for FIND. The two work the same way, except FIND matches capitals exactly and doesn't accept wildcards:

=FILTER(A2:C8,ISNUMBER(FIND("Desk",A2:A8)))

Now the result is Desk lamp and Desk organiser. "Standing desk" is left out because its "desk" is lowercase. Use FIND when capitals carry meaning, such as product codes where "AB" and "ab" are different. For ordinary product names and descriptions, SEARCH is usually the better choice, because people rarely type capitals consistently. The comparison of FIND and SEARCH explains the other differences.

How to filter by several words#

Add the tests for OR, and multiply them for AND, just as with any FILTER with more than one condition.

Contains desk or lamp:

=FILTER(A2:C8,ISNUMBER(SEARCH("desk",A2:A8))+ISNUMBER(SEARCH("lamp",A2:A8)))

This returns four rows: Standing desk, Desk lamp, Desk organiser and Floor lamp. Desk lamp matches both words but appears only once.

Contains desk and costs under 100:

=FILTER(A2:C8,ISNUMBER(SEARCH("desk",A2:A8))*(C2:C8<100))

This returns Desk lamp (39) and Desk organiser (25). The guide to FILTER with multiple criteria explains why + means OR and * means AND.

How to filter rows that do not contain text#

Wrap the test in NOT to flip TRUE and FALSE:

=FILTER(A2:C8,NOT(ISNUMBER(SEARCH("desk",A2:A8))))

The result is every product without "desk": Office chair, Monitor arm, Floor lamp and Laptop stand. ISERROR gives the same result in one step, ISERROR(SEARCH("desk",A2:A8)), because SEARCH returns an error exactly where the word is missing.

How to build a search box#

Point SEARCH at a cell, so anyone can type a word and see matching rows. If E1 is the search box:

=FILTER(A2:C8,ISNUMBER(SEARCH(E1,A2:A8)),"No matches")

Type "lamp" in E1 and the list shows the two lamps. Type "stand" and you get Standing desk and Laptop stand, because "stand" appears inside both names. The third argument shows "No matches" instead of #CALC! when nothing is found.

One surprise: if E1 is empty, every row appears. SEARCH treats empty text as found at position 1, so every test is TRUE. That's often what you want for a search box, a full list until someone types.

To match only names that start with the search word, compare the first characters instead: =FILTER(A2:C8,LEFT(A2:A8,LEN(E1))=E1). With "desk" in E1, that returns Desk lamp and Desk organiser, but not Standing desk.

How to filter by text without a formula#

If you only need a quick look, use the filter buttons instead. Select the table, press Ctrl+Shift+L (Mac: ⌘+Shift+F), click the arrow on the Product column, and choose Text Filters > Contains. Type "desk" and click OK.

This hides the other rows in place rather than copying matches somewhere else. It works in every Excel version, but it doesn't update when the data changes, and it can't feed other formulas. To total the matching rows with a formula, see using FILTER with SUM. To filter on dates, see filtering by date.

Common problems with FILTER contains#

Most problems come from the error-handling step or the spill area. Check these:

1. Missing ISNUMBER. FILTER(A2:C8,SEARCH("desk",A2:A8)) returns an error, because the SEARCH errors pass straight through.
2. #CALC! Nothing matched. Add a third argument such as "No matches".
3. #SPILL! Cells in the result area aren't empty. Clear them.
4. Too many matches. Short words match inside longer ones: "arm" also finds "Farm" or "Warm". Add spaces, like " arm", or use FIND for exact capitals.
5. #NAME? Your Excel is older than 2021 and doesn't have FILTER. Use the filter buttons instead.

Practise it#

Text searches with FILTER are a common task in data analyst tests, and they combine two skills at once. Practise the FILTER function in a live spreadsheet, then practise FIND and SEARCH to master text matching. Each formula is checked instantly. Or try the exercise on this page first.