Excel FILTER Function: Find Rows Using Partial Text

Formulas & functions · Updated November 19, 2026

Yes. In Excel for Microsoft 365, FILTER can return rows whose text contains a word or phrase by combining it with SEARCH. With records in A2:C6, the text to search in B2:B6, and the search term in E2, use =FILTER(A2:C6,ISNUMBER(SEARCH(E2,B2:B6)),"No matches"). The result includes every row where the contents of column B contain the term in E2.

What the partial-text filter returns

FILTER is a dynamic array function: it evaluates a condition for each row and returns the rows that meet it. The formula above returns all three columns from A2:C6, not just the matching text in column B. That is useful when column A holds an ID and column C holds a category or date you also need to see alongside each match.

For example, imagine B2:B6 contains Red apple, Green apple, Pineapple, Orange juice, and Apple cider. If E2 contains apple, the results include Red apple, Green apple, Pineapple, and Apple cider. SEARCH looks for the character sequence anywhere in each cell, so it finds apple inside pineapple as well as when apple is a separate word. It does not require the cell to equal the search term.

The formula assumes the data rows and the search range line up: A2:C6 has five rows, as does B2:B6. If those ranges have different heights, FILTER can return a #VALUE! error because its include array must correspond to the rows being filtered.

How FILTER, SEARCH, and ISNUMBER work together

FILTER needs an include argument that identifies which rows to keep. SEARCH(E2,B2:B6) checks each cell in the text range and returns the position where the search term begins. A match at the first character returns 1; a match later in the text returns another positive number. When SEARCH cannot find the term, it returns a #VALUE! error for that cell.

ISNUMBER converts those results into TRUE or FALSE values. A position returned by SEARCH becomes TRUE, while the not-found error becomes FALSE. FILTER then keeps rows associated with TRUE and leaves out rows associated with FALSE. The third argument, "No matches", supplies a readable result if nothing qualifies instead of letting FILTER return its usual empty-result error.

SEARCH is not case-sensitive, so apple matches Apple and APPLE. That is usually convenient for a search box. The formula also returns the original cell values and row data; it does not remove the matched text or alter the source range.

Set up the formula in a worksheet

  1. Arrange the source data in a continuous range with one record per row. In this example, the records occupy A2:C6, and the text to search is in B2:B6.
  2. Choose a separate cell, such as E2, for the word or phrase a user will enter. Keep that cell outside the source data and the area where results will spill.
  3. Select an empty cell with room below and to the right, such as G2, and enter the formula from the opening example. Press Enter.
  4. Type a test term such as apple in E2. Excel recalculates the formula and displays matching source rows beginning at G2.

Do not type the result formula into each output row or drag it down as you would with a conventional row formula. FILTER returns a spilled array from one formula cell. If the output needs more space, clear cells in the spill area; Excel cannot place results over existing content and may show a #SPILL! error. A border or other formatting in the area is generally fine, but values in the way are not.

Case sensitivity and wildcard characters

SEARCH ignores capitalization. If capitalization must matter, use FIND instead; FIND is case-sensitive and otherwise serves the same role in this kind of include test. With records in A2:C6, text in B2:B6, and the case-sensitive search term in E2, use =FILTER(A2:C6,ISNUMBER(FIND(E2,B2:B6)),"No matches"). For instance, a search for Apple can match Apple cider but not apple cider.

SEARCH also recognizes the wildcard characters * and ?. An asterisk represents any number of characters, while a question mark represents one character. This means a search term containing one of these characters may behave as a pattern rather than as literal text. To search for a literal wildcard character, precede it with a tilde: ~* searches for an asterisk and ~? searches for a question mark. A tilde itself can be escaped as ~~. This behavior comes from SEARCH, not from FILTER.

For ordinary words and phrases, wildcard handling is usually irrelevant. It matters when a user may enter symbols such as ? or * and expects them to be treated as ordinary characters. Consider explaining the wildcard rule beside a search box, or use a different search design if literal matching of arbitrary user-entered symbols is essential.

Combine partial text with another condition

You can make the include test require both a partial-text match and another condition. For example, suppose column C contains a category, and F2 contains the category to select. With the same A2:C6 data, text in B2:B6, search term in E2, and category in F2, use =FILTER(A2:C6,(ISNUMBER(SEARCH(E2,B2:B6)))*(C2:C6=F2),"No matches"). Multiplying the two TRUE/FALSE tests acts as AND: a row is returned only when its text contains E2 and its category equals F2.

For an either/or test, add the two match tests and check whether the sum is greater than zero. With text in B2:B6 and two terms in E2 and F2, this include expression can be used in FILTER: (ISNUMBER(SEARCH(E2,B2:B6))+ISNUMBER(SEARCH(F2,B2:B6)))>0. It keeps a row if either term appears. To use it, place that expression as FILTER’s second argument and retain the same aligned source range. If the terms may be blank, handle that case deliberately: SEARCH with an empty search string can behave like a match across the range.

Blank searches, no matches, and common errors

A blank search term is a common surprise. SEARCH treats an empty string as found, so the basic formula can return every row when E2 is blank. If a blank input should prompt the user rather than show all records, wrap the formula in an IF test. With the same A2:C6, B2:B6, and E2 layout, use =IF(E2=","Enter a search term",FILTER(A2:C6,ISNUMBER(SEARCH(E2,B2:B6)),"No matches")). When E2 is empty, this displays a prompt; otherwise, it runs the filter. In Excel syntax, the empty-string test is written as two quotation marks between the equals sign and comma; the complete formula is =IF(E2="","Enter a search term",FILTER(A2:C6,ISNUMBER(SEARCH(E2,B2:B6)),"No matches")).

If you see #SPILL!, inspect the cells where the results should appear and clear any blocking values or merged cells. If you see #VALUE!, check that the source range and include range have the same number of rows, and confirm the formula has not accidentally included a header in only one range. If FILTER shows No matches, first check spelling, extra spaces in the search term, and whether the selected text column is the one that actually contains the wording.

When data is stored in an Excel Table, structured references can make formulas easier to maintain as rows are added, but the ranges still need to describe corresponding rows. The basic cell-range formula is often simpler to understand while learning. Also note that results spill into neighboring cells; they cannot be placed inside the body of an Excel Table as a dynamic array output.

Excel version and alternatives

FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. The formula approach is the same in current Excel for Windows and Mac. If a workbook is opened in an older Excel version that does not support FILTER, the function may display as an unsupported function rather than produce a spilled result. The SEARCH function itself exists in older Excel versions, but that does not make FILTER available there.

If you need a one-time view rather than a formula-driven result, use the table or range’s built-in text filtering instead. In Excel for Windows, open the filter dropdown on the relevant column and use Text Filters to choose a condition such as Contains, then enter the word or phrase. This hides nonmatching rows in place rather than creating a separate live output. The menu labels and placement can vary somewhat in Excel for Mac and the web, but the filter dropdown offers text-filter options there as well.

Choose FILTER when you want a separate result that updates as the source data or search term changes. Use the built-in column filter when you want to inspect or temporarily hide rows in the original list. For a long list, keeping the data in a well-structured Table and avoiding unnecessarily large whole-column calculations can make the workbook easier to maintain. If a formula uses commas but Excel reports a syntax issue, the computer’s regional settings may require semicolons as argument separators; the formula examples here use the standard comma syntax.

Frequently Asked Questions

Can Excel FILTER search for part of a word?
Yes. Put SEARCH inside FILTER’s include argument, wrapped in ISNUMBER. SEARCH finds the text sequence anywhere in each cell, so a search for app can match apple and application.

Does FILTER with SEARCH distinguish uppercase and lowercase?
No. SEARCH is case-insensitive. Replace SEARCH with FIND when capitalization must match; FIND is case-sensitive.

Can FILTER match text in more than one column?
Yes. Build a TRUE/FALSE test for each text column and combine the tests with addition for an either-column match, or multiplication when both columns must match. Ensure each test covers the same rows as the source array.

Why does my formula return every row when the search box is empty?
An empty search string can be treated as present in every text value. Test the search cell with IF and show a prompt or blank result when it is empty, rather than passing the empty value directly to SEARCH.

Why do I get #SPILL! instead of matching rows?
The formula’s output range is blocked by existing content or another layout restriction. Clear the cells where results need to appear, then let the single FILTER formula spill into that space.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.