Excel FILTER Function: Filter Rows by Partial Text

Formulas & functions · Updated November 19, 2026

To return rows where a column contains part of a word or phrase, combine Excel’s FILTER function with SEARCH. For example, with records in A2:C100, the text to find in E1, and the text being searched in B2:B100, use =FILTER(A2:C100,ISNUMBER(SEARCH(E1,B2:B100)),"No matches").

This formula returns every matching row, not just the first one. It is useful for searching descriptions, names, product lists, and other text when you know only a fragment of the value. The method works in Microsoft 365 for Windows and uses dynamic arrays, so results spill into nearby cells.

How the partial-text formula works

FILTER returns items from an array when the corresponding entries in an include argument are TRUE. Here, SEARCH looks for the text in E1 within each cell in B2:B100. It returns a number indicating the position where the text starts, or an error if that text is not found. ISNUMBER turns those results into TRUE or FALSE values that FILTER can use.

For example, if E1 contains cable, a cell containing “USB-C cable” is a match even though it has other words. A cell containing “adapter” is not. SEARCH is not case-sensitive, so it also finds “Cable” and “CABLE.” This is the key difference between a partial-text search and a comparison that requires the entire cell to equal the search term.

The third argument, "No matches", is optional. It tells FILTER what to display if no rows meet the condition, rather than showing a calculation error. The ranges in the first and second arguments must cover the same number of rows; otherwise, Excel can return a #VALUE! error.

Set up a simple search

Put the records you want returned in one rectangular range, and keep the search text in a separate cell. In this example, columns A:C contain an ID, product, and note; column B is the column to search. E1 is the search box. Enter the formula in an empty cell outside the source data:

With records in A2:C6, product names in B2:B6, and the search word in E1, use =FILTER(A2:C6,ISNUMBER(SEARCH(E1,B2:B6)),"No matches"). If E1 contains cable and the product cells are “USB-C Cable,” “HDMI Adapter,” “Braided cable,” “Wireless Mouse,” and “Ethernet cable,” the result contains the first, third, and fifth rows, in their original order.

To build it step by step:

  1. Place the rows to return in a consistent range, such as A2:C100, with a header row above it.
  2. Choose the column that contains the text to search, such as B2:B100.
  3. Enter a search word or phrase in a separate cell, such as E1.
  4. Enter the formula in a clear area where the returned rows have room to spill.
  5. Change E1 to search for another fragment; the displayed results update automatically.

The formula returns the full selected rows in A:C, even though it checks only column B. If you want to return just the matching product names, use B2:B100 as the first argument instead of A2:C100, while keeping B2:B100 as the search range.

Search more than one column

A common follow-up is whether a partial word can be searched in several fields at once. Yes. Add the TRUE/FALSE tests for each column, then check whether their sum is greater than zero. This treats a match in either column as a match for the row.

With records in A2:C100, text to find in E1, and possible matches in columns B and C, use =FILTER(A2:C100,(ISNUMBER(SEARCH(E1,B2:B100))+ISNUMBER(SEARCH(E1,C2:C100)))>0,"No matches"). A row appears if the fragment is found in either B or C, or in both. The ranges must still have matching row counts.

You can also require two conditions at the same time. For instance, you might search for a product fragment in column B and a location fragment in column C. Multiplying the TRUE/FALSE tests makes both conditions necessary: TRUE multiplied by TRUE is 1, while a FALSE condition makes the product 0.

With records in A2:C100, the product fragment in E1, and the location fragment in F1, use =FILTER(A2:C100,ISNUMBER(SEARCH(E1,B2:B100))*ISNUMBER(SEARCH(F1,C2:C100)),"No matches"). A row is returned only when both searches succeed. This is an AND test; adding tests and checking for a positive result, as in the previous formula, is an OR test.

Case-sensitive searches and search behavior

SEARCH ignores capitalization. That is usually convenient for names and descriptions, where “north,” “North,” and “NORTH” are generally intended to match. If capitalization must match exactly, use FIND in place of SEARCH; the rest of the formula can stay the same.

With records in A2:C100, the text to find in E1, and the text being checked in B2:B100, use =FILTER(A2:C100,ISNUMBER(FIND(E1,B2:B100)),"No matches"). FIND is case-sensitive. If E1 contains “Cable,” it can match “Cable” but not “cable.” It still searches within a cell, rather than requiring the entire cell to equal the search text.

One less obvious detail is that SEARCH recognizes the wildcard characters * and ?. An asterisk can stand for any number of characters, and a question mark can stand for one character. That can be useful when you want a pattern, but it can produce surprising results if the search box contains one of those characters and you meant it literally. A tilde (~) is used to escape a wildcard in SEARCH; for example, ~* searches for a literal asterisk.

Handle an empty search box

An empty search cell can be confusing: SEARCH with an empty string can match every text value, making FILTER appear to return the whole list. If you would rather show a prompt until someone enters a search term, add an IF check around the formula.

With source rows in A2:C100, the searched text in B2:B100, and the search box in E1, use =IF(E1="","Enter a search term",FILTER(A2:C100,ISNUMBER(SEARCH(E1,B2:B100)),"No matches")). When E1 is blank, Excel displays the prompt; otherwise, it performs the partial-text search and displays matching rows or “No matches.” This is often clearer in a worksheet used by other people.

Common errors and practical limits

If the formula does not behave as expected, check the data ranges and the space around the formula first. FILTER returns a dynamic array, so the result needs an unobstructed area to spill. If any cell in the intended output area is occupied, Excel may show #SPILL!. Clear the obstructing cells, or move the formula to a larger empty area. A spill formula also cannot spill from inside an Excel Table; place it outside the table.

Other checks that often solve problems include:

  • Confirm that the searched range and returned range start and end on the same rows.
  • Check for unexpected spaces in the search term or source data. A leading or trailing space is part of the text being searched.
  • Make sure the search text is in the cell referenced by the formula, such as E1, rather than in a different input cell.
  • Use the optional third argument of FILTER to provide a no-results message instead of an error when no match exists.
  • Check that your Excel version supports dynamic arrays. FILTER is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.

In some regional settings, Excel uses semicolons rather than commas to separate formula arguments. If a formula copied from an example is rejected despite having the correct function names, your installation may require semicolons as separators. The formulas shown here use commas, as is typical in English-language Excel settings.

FILTER versus other ways to find text

Use FILTER when you want a formula-driven list of all matching records that updates as the source data or search term changes. It preserves the source order and returns entire rows or selected columns, depending on the first argument. It does not alter or remove the original records.

For a one-time inspection, Excel’s table filter can be quicker: open the filter dropdown for the relevant column, choose Text Filters > Contains, and enter the fragment. That filters the visible rows in place rather than creating a separate results array. It is convenient for manual work, but it is not the same as a search cell controlling a live formula result.

For recurring imports and transformations, Power Query may be a better fit than a worksheet formula, especially when you need a repeatable process involving many cleanup steps. But for an interactive “type a fragment and show matching rows” worksheet, FILTER with SEARCH is usually the more direct choice. Avoid using an exact-match lookup when the requirement is to find a fragment: exact lookups answer a different question and can miss cells that contain extra text.

Frequently Asked Questions

Can Excel FILTER search for part of a word?

Yes. Use ISNUMBER(SEARCH(search_text, text_range)) as FILTER’s include condition. SEARCH finds the fragment anywhere in each cell, and FILTER returns the corresponding rows.

Is SEARCH case-sensitive in Excel?

No. SEARCH ignores capitalization. Use FIND instead if uppercase and lowercase letters must be treated differently.

Can FILTER return rows when the text appears in either of two columns?

Yes. Add the two partial-match tests and check that the sum is greater than zero. That makes a match in either column sufficient; multiplying the tests instead requires both to match.

Why does my FILTER formula show #SPILL!?

Excel cannot place the dynamic-array result because one or more cells in its spill area are occupied, or the formula is inside a table. Clear the destination area or place the formula outside the table.

Does the FILTER function work in older Excel versions?

FILTER is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 and earlier perpetual releases. In older versions, use a table’s Text Filters > Contains option for manual filtering, or consider Power Query for repeatable tasks.

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.