Excel FILTER Formula: Find Partial Text Matches

Formulas & functions · Updated November 19, 2026

To return rows where a cell contains part of a word or phrase, combine Excel’s FILTER and SEARCH functions: =FILTER(A2:C6,ISNUMBER(SEARCH("west",B2:B6)),"No matches"). In this example, Excel returns rows whose column B text contains “west,” regardless of capitalization.

How FILTER finds a partial-text match

FILTER returns rows or columns from a range when a corresponding condition is true. The condition must produce a set of TRUE or FALSE results, one for each row to check. SEARCH looks for a text fragment inside each cell and returns its position if it finds the fragment. If it does not, it returns an error. ISNUMBER turns those positions into TRUE and those errors into FALSE, which gives FILTER a usable inclusion test.

For example, if the search term is “west,” SEARCH finds it in “West Coast cable,” “Northwest switch,” and “Western power cord.” The text does not have to match the entire cell: it can appear at the start, middle, or end. The search is not case-sensitive, so “WEST,” “West,” and “west” all match.

The FILTER function is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It returns a dynamic array: the matching results spill into neighboring cells automatically and update when the source data changes. You do not need to copy the formula down or press Ctrl+Shift+Enter.

Build the basic formula

Suppose a worksheet has order numbers in A2:A6, descriptions in B2:B6, and statuses in C2:C6. The rows are 101, “West Coast cable,” Open; 102, “Eastern adapter,” Closed; 103, “Midwest router,” Open; 104, “Northwest switch,” Open; and 105, “Western power cord,” Closed. To return all three columns for descriptions containing “west,” enter this formula in an empty cell:

With order data in A2:C6 and descriptions in B2:B6:

=FILTER(A2:C6,ISNUMBER(SEARCH("west",B2:B6)),"No matches")

The result contains the full rows for orders 101, 104, and 105. The first argument, A2:C6, is the range to return. The second, ISNUMBER(SEARCH("west",B2:B6)), checks each description. The third argument, “No matches,” is optional text Excel displays if nothing satisfies the condition. If you omit it and there are no matches, Excel returns a #CALC! error.

Put the formula somewhere with enough empty cells to its right and below for the results. If an existing value blocks the output, Excel displays #SPILL!. Clear the obstructing cells, or move the formula. A spilled result also cannot expand inside an Excel table, so place the formula outside the table area.

Use a cell as the search term

A cell reference makes the results easier to change than editing the formula each time. For instance, enter a word or phrase in E1 and refer to that cell in SEARCH.

With data in A2:C6, descriptions in B2:B6, and the search phrase in E1:

=FILTER(A2:C6,ISNUMBER(SEARCH(E1,B2:B6)),"No matches")

Typing “west” in E1 returns orders 101, 104, and 105. Change E1 to “adapter” and the result becomes the row for order 102. A blank search cell is a special case: searching for an empty string can match every text cell. If users might leave E1 blank, add a separate check or provide instructions to enter a search term before relying on the output.

Choose between case-insensitive and case-sensitive search

SEARCH ignores capitalization. That is usually useful for names, descriptions, and category labels, because it avoids missing a match just because someone typed a different case. It also treats the characters * and ? as wildcard characters. If the search term itself may include those characters and you want them treated literally, that behavior needs extra handling.

For a case-sensitive match, use FIND instead. It returns a position only when the capitalization matches as typed.

With descriptions in B2:B6 and the rows to return in A2:C6:

=FILTER(A2:C6,ISNUMBER(FIND("West",B2:B6)),"No matches")

For the sample data, this returns orders 101 and 105: “West Coast cable” and “Western power cord” begin with a capital W, while “Northwest switch” has a lowercase w in the matching part. Use FIND only when case is meaningful; otherwise, SEARCH is generally the more forgiving choice.

Combine partial text with other conditions

You can add conditions to the inclusion test. Multiplying TRUE/FALSE arrays works like AND: a row is included only when every condition is true. Adding condition arrays and checking whether the sum is greater than zero works like OR: a row is included if at least one condition is true.

For example, return descriptions containing “west” only when the status is Open. With rows in A2:C6, descriptions in B2:B6, and statuses in C2:C6:

=FILTER(A2:C6,ISNUMBER(SEARCH("west",B2:B6))*(C2:C6="Open"),"No matches")

This returns orders 101 and 104. The text test is true for three orders, but order 105 has a Closed status, so the multiplication excludes it. Ensure that the ranges in each condition cover the same rows and are the same size as the rows in the return range.

To match either “west” or “north,” use an OR test. With descriptions in B2:B6 and complete records in A2:C6:

=FILTER(A2:C6,((ISNUMBER(SEARCH("west",B2:B6)))+(ISNUMBER(SEARCH("north",B2:B6))))>0,"No matches")

This returns orders 101, 103, 104, and 105: the Midwest description matches “west,” while Northwest matches both terms but appears only once. The greater-than-zero test converts the added TRUE/FALSE results back into a Boolean inclusion array.

Common errors and ways to fix them

  • #SPILL!: One or more cells in the output area are occupied, merged, or otherwise preventing the result from spilling. Clear the destination area or move the formula. Keep in mind that the formula itself should be outside an Excel table if it needs to return multiple cells.
  • #CALC! when there are no matches: Add the optional third argument to FILTER, such as “No matches,” or adjust the search term and source data. The fallback appears only when the filter finds no rows; it does not hide unrelated formula errors.
  • Unexpected matches: Check whether SEARCH is finding the fragment inside a longer word. A search for “art,” for example, also matches “carton.” If you need a whole word rather than any substring, a simple partial-text test may not express that requirement reliably; use a delimiter-aware method suited to how the data is stored.
  • No matches even though text looks the same: Check for leading or trailing spaces, nonprinting characters, spelling differences, and whether the formula points to the intended column. SEARCH ignores case, but it does not ignore extra characters within a phrase.
  • Errors in the source cells: An error in a cell being searched can affect the condition array and therefore the formula. Correct the source error or deliberately handle it in a separate cleanup step rather than assuming the no-match argument will catch it.

FILTER formula versus Excel’s built-in filter

A formula is useful when you need a separate, live list of matching records for a report, dashboard, or further calculations. The source rows remain visible, and the output refreshes as the source values change. You can also combine the text condition with status, date, or other criteria in one expression.

For a quick, interactive view of the original data, use the built-in filter instead. In Excel for Microsoft 365 on Windows, select the data range or a cell in it, then choose Data > Filter. Open the dropdown for the text column and choose Text Filters > Contains, then enter the fragment. This hides nonmatching rows in place rather than producing a second list. Use the formula when you need reusable output; use the built-in filter when you want to inspect or edit the original rows.

Version and platform notes

The formula approach relies on FILTER, which is supported in Microsoft 365 and Excel 2021 or later. In older Excel versions that do not support dynamic arrays, the formula will not behave as a spilling filter; use the built-in filter or another method available in that version. Excel for Mac and Excel for the web support FILTER in supported versions, with the same basic formula structure. Ribbon layout and some interaction details can differ by platform.

Excel normally uses commas between formula arguments in the examples here. A computer’s regional settings can cause Excel to expect semicolons instead; if Excel flags a correctly copied formula as invalid, check the list separator used by that installation. The function names may also be localized in some language editions.

Frequently Asked Questions

Can Excel FILTER search for text anywhere in a cell?
Yes. Use SEARCH inside ISNUMBER as the inclusion condition. It checks for a substring anywhere in each cell rather than requiring the whole cell to equal the search phrase.

Is SEARCH case-sensitive in Excel?
No. SEARCH is case-insensitive. Use FIND instead if uppercase and lowercase letters must match exactly.

Can I make the search term come from a cell?
Yes. Put the term in a cell such as E1 and use that reference inside SEARCH. This lets you update the filtered results by changing the cell value.

Why does my FILTER formula show #SPILL!?
The output range is blocked, or the formula is positioned where a dynamic array cannot expand. Clear cells in the spill area, check for merged cells, or place the formula outside the table.

Can I filter for two partial-text terms?
Yes. Use addition between the two TRUE/FALSE text tests and compare the result with zero to include rows matching either term. Use multiplication between conditions when a row must meet both tests.

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.