Excel FILTER Function: Find Cells That Contain Text

Formulas & functions · Updated November 19, 2026

Yes. In Excel for Microsoft 365, FILTER can return rows where a cell contains particular text by combining it with SEARCH. With product names in A2:A7 and full records in A2:B7, this formula returns the rows containing “mug”: =FILTER(A2:B7,ISNUMBER(SEARCH("mug",A2:A7)),"No matches"). SEARCH checks for the phrase inside each product name, and FILTER returns the matching records.

What “contains text” means in Excel

Excel has two related ways to filter text, and it helps to separate them. The Filter command on the Data tab lets you narrow a table interactively; its text menu includes a Contains condition. The FILTER function, by contrast, is a formula that returns a new, automatically updating set of rows elsewhere in the worksheet. This article focuses on the function, while also explaining when the menu command may be simpler.

The FILTER function does not have a built-in “contains” option of its own. Instead, SEARCH tests each cell for a phrase and produces a position number when it finds one. ISNUMBER turns those results into TRUE or FALSE values that FILTER can use to decide which rows to return. A match can occur anywhere in a cell: searching for “mug” matches “Blue mug,” “Travel mug,” and “mug set.”

For example, imagine A2:B7 contains these records: Blue mug, 12; Green mug, 8; Blue notebook, 20; Travel mug, 5; Desk lamp, 7; and Red mug, 11. The formula in the opening paragraph returns four rows—Blue mug, Green mug, Travel mug, and Red mug—with their corresponding quantities. The original records stay in place; the result is a separate spill range.

How the formula works

FILTER uses the syntax =FILTER(array,include,[if_empty]). The array is the data to return, include is a matching test with one TRUE or FALSE result per row, and if_empty is an optional value to show if nothing matches. In the example, A2:B7 is the array, while ISNUMBER(SEARCH(“mug”,A2:A7)) creates the include test. The text “No matches” is the optional fallback.

SEARCH is not limited to whole-cell matches. It locates a phrase within a longer entry and ignores letter case, so “mug” also matches “MUG” or “Mug.” If the phrase is absent, SEARCH returns an error for that cell; ISNUMBER evaluates the search result as a match or non-match for FILTER. You do not need to add a separate comparison with TRUE.

The source and include ranges must have corresponding rows. If the returned data is A2:B20, use a search range such as A2:A20, not A2:A19 or A2:A21. A mismatch in their dimensions can make FILTER return a calculation error. Keep the ranges aligned when extending a formula to more records.

Build a reusable search formula

A fixed search phrase is useful for a one-off result, but a cell reference makes the formula interactive. Put a phrase such as mug in E2, then use the formula below. With product names in A2:A7, records in A2:B7, and the search term in E2, it returns the same four matching rows for the example dataset:

=FILTER(A2:B7,ISNUMBER(SEARCH(E2,A2:A7)),"No matches")

Change E2 to lamp, and the result becomes the Desk lamp record. The formula recalculates when E2 changes or when a source value changes. To make the search cell absolute when copying the formula, use $E$2; the dollar signs keep that reference fixed. Usually, however, a single dynamic formula is placed once and allowed to spill, rather than copied down like a conventional row formula.

Keep the output area clear. FILTER returns as many rows and columns as needed, so any existing value in the spill range can cause a #SPILL! error. Put the formula outside the source data and leave enough empty worksheet space for its results. Dynamic array formulas also cannot spill from inside an Excel Table; place the formula in a normal cell outside the table.

Choose case sensitivity and matching behavior

SEARCH is case-insensitive, which is normally what people expect from a text filter. If capitalization matters, use FIND instead. With product names in A2:A7 and complete records in A2:B7, this formula looks for the exact capitalization “Blue” and returns Blue mug and Blue notebook:

=FILTER(A2:B7,ISNUMBER(FIND("Blue",A2:A7)),"No matches")

FIND is case-sensitive, so it will not match “blue mug.” It still searches within the cell rather than requiring the entire cell to equal the search phrase. For exact whole-cell equality, use a direct comparison as the include test, such as A2:A7=E2, instead of SEARCH or FIND.

SEARCH also recognizes wildcard characters: an asterisk represents any number of characters, and a question mark represents one character. That can be handy for flexible patterns, but it can surprise you if the phrase you intend to find literally contains * or ?. A tilde can escape a wildcard character in SEARCH. FIND does not treat those characters as wildcards.

Combine more than one text condition

You can require multiple phrases to appear in the same cell by multiplying the TRUE/FALSE tests. Multiplication acts like AND: a row is included only when every test is TRUE. With the example records in A2:B7, the formula below returns Blue mug because it contains both “blue” and “mug”:

=FILTER(A2:B7,ISNUMBER(SEARCH("blue",A2:A7))*ISNUMBER(SEARCH("mug",A2:A7)),"No matches")

To accept either phrase, add the tests instead. With the same ranges, the following formula returns the four mug records plus Desk lamp, because a match for either “mug” or “lamp” is enough:

=FILTER(A2:B7,(ISNUMBER(SEARCH("mug",A2:A7))+ISNUMBER(SEARCH("lamp",A2:A7)))>0,"No matches")

For conditions in different columns, the same AND/OR idea applies. For example, multiply a text-match test on the product-name column by a comparison on a category column to require both conditions. Check that each test covers the same rows, and use parentheses to make the intended grouping clear. In complicated formulas, line breaks in the formula bar or a helper column can make each condition easier to inspect.

Common problems and practical fixes

  • #SPILL! means Excel cannot place the result in the cells it needs. Clear obstructing values, merged cells, or other conflicts in the output area, and put the formula outside an Excel Table.
  • #CALC! can appear when there are no matches and the formula has no if_empty argument. Supply a fallback such as “No matches,” or choose another meaningful message for your worksheet.
  • Unexpectedly many matches: SEARCH ignores capitalization and looks for a substring. A search for “art,” for instance, also finds that sequence inside a longer word. Use FIND if case matters, or an equality test if you need an exact whole-cell match.
  • Unexpectedly no matches: check for extra spaces, spelling differences, and whether the search term is actually in the searched column. SEARCH looks within the cells you specify, not every column in the returned array.
  • Errors in source cells: an error value in the search range can interfere with the include test. If the data legitimately contains errors, handle them deliberately rather than hiding all errors without checking their cause.

A blank search term needs special attention. SEARCH with an empty string effectively finds a match in every text cell, so a blank input cell may return the whole dataset rather than no rows. If users can leave the search box empty, decide what the sheet should do in that case—for example, show all records or display a prompt—and build that behavior into the formula.

Also distinguish a missing match from a bad formula. The optional if_empty argument handles a valid search that finds nothing; it does not repair mismatched ranges, blocked spill cells, or errors in the source. Fix those underlying issues separately so that a fallback message does not give the impression that the data simply had no matches.

FILTER function versus the Data tab filter

Use the FILTER function when you want a formula-driven result that can feed a report, dashboard, or another calculation. It updates as the source data or search term changes and leaves the original list intact. The formula is also easy to combine with other conditions, although readers may need to understand the formula to maintain it.

Use the built-in filter when you want to inspect or temporarily narrow the original list without creating a second output range. Select a cell in the data, choose Data > Filter, open the dropdown for the text column, then choose Text Filters > Contains and enter the phrase. This hides nonmatching rows rather than returning a separate array. It is often the quickest choice for manual exploration or a one-time check.

The menu filter is convenient for people who do not need a live formula result. FILTER is more suitable when the displayed matches must recalculate automatically or be used elsewhere. Neither approach changes the underlying text; they only control which records are shown or returned.

Availability and workbook compatibility

FILTER is available in Excel for Microsoft 365 and Excel 2021 or later, including Excel for the web and current Excel for Mac versions. The core formula and its SEARCH-based condition work the same way on those platforms. In Windows Excel, function arguments are normally separated by commas as shown; regional settings can use a different list separator, so follow the separator Excel expects if the formula is rejected.

Excel 2019 and earlier do not include the FILTER function. A workbook containing a FILTER formula may show a compatibility or name error when opened in an older edition. If someone using an older version must work with the results, consider the Data tab filter, a helper column with a text test, or sharing a static copy of the filtered results. A static copy will not update when the source data changes.

Frequently Asked Questions

Can Excel FILTER find text anywhere in a cell?
Yes. Combine FILTER with SEARCH and ISNUMBER. SEARCH looks for the phrase anywhere inside each selected cell, and FILTER returns the corresponding rows.

Is the Excel FILTER text search case-sensitive?
No, not when you use SEARCH. Replace SEARCH with FIND when uppercase and lowercase letters must be distinguished.

Can I filter for two words at once?
Yes. Multiply two ISNUMBER(SEARCH(…)) tests to require both phrases, or add the tests and compare the result with zero to accept either phrase.

Why does FILTER return #SPILL!?
One or more cells needed for the returned array are occupied or otherwise unavailable. Clear the output area, and make sure the formula is not inside an Excel Table.

Does FILTER work in Excel 2019?
No. FILTER requires Excel 2021 or Microsoft 365. In older versions, use the Data tab’s filter controls or another compatible approach.

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.