To return rows where a column contains particular text, combine Excel’s FILTER function with SEARCH: with records in A2:C100 and the text to find in B2:B100, enter =FILTER(A2:C100,ISNUMBER(SEARCH("urgent",B2:B100)),"No matches"). SEARCH finds the text within each cell without regard to capitalization, and FILTER returns the matching rows as a spilling result.
The basic formula and what it returns
FILTER needs a range to return and a condition that identifies which rows to keep. SEARCH checks each cell for the requested text. When it finds the text, SEARCH returns a number indicating its position; when it does not, it returns an error. ISNUMBER turns those results into TRUE or FALSE values that FILTER can use as its include condition.
For a small example, suppose A2:C6 contains an ID, a description, and a status. The descriptions in B2:B6 are “Urgent: renew permit,” “Routine inspection,” “URGENT – call supplier,” “Permit archived,” and “Review urgent request.” With that layout, this formula returns the matching rows:
With IDs, descriptions, and statuses in A2:C6, and descriptions in B2:B6, use:
=FILTER(A2:C6,ISNUMBER(SEARCH("urgent",B2:B6)),"No matches")
The result includes the first, third, and fifth records because each description contains “urgent.” SEARCH is case-insensitive, so it finds both lowercase and uppercase versions. The optional third argument displays “No matches” instead of the #CALC! error that FILTER otherwise returns when no records meet the condition.
Enter a search term in a worksheet cell
Typing the search term directly into a formula is convenient for a fixed test, but putting it in a worksheet cell makes the result easier to reuse. For example, place the term in E1 and change it whenever you want to inspect a different set of records. Keep the source data and search cell outside the result area so the output can expand without overwriting anything.
With records in A2:C100, descriptions in B2:B100, and the requested text in E1, enter:
=FILTER(A2:C100,ISNUMBER(SEARCH(E1,B2:B100)),"No matches")
When E1 contains “permit,” the formula returns every row whose description includes that sequence of letters, including a longer word such as “permits.” The search is a substring search, not a whole-word search. If the word appears in the middle or at the end of a cell, the row still qualifies.
FILTER is available in Microsoft 365 and Excel 2021 or later. In Microsoft 365 for Windows, type the formula in a cell outside the source data and press Enter; matching rows spill into adjacent cells. The function is also available in current Microsoft 365 editions for Mac and Excel for the web, although ribbon details and the surrounding interface can differ. A dynamic array formula should be entered in a regular worksheet range, not inside an Excel table column.
How SEARCH and FILTER work together
SEARCH is the part that tests for text; FILTER is the part that returns the relevant records. The two ranges must line up row by row. If the returned data covers rows 2 through 100, the SEARCH range should also cover rows 2 through 100. A mismatched range can produce an error or an incorrect include array.
- SEARCH(text, within_text) looks for text inside another text value and ignores capitalization.
- ISNUMBER(…) converts a successful position into TRUE and a failed search into FALSE.
- FILTER(array, include, [if_empty]) returns rows from the array where the corresponding include value is TRUE.
The formula returns complete rows because its first argument is A2:C100. To return only the description column, use B2:B100 as the array instead, while keeping the same-sized SEARCH range. The formula recalculates when source cells or the search text change, so it can serve as a live view rather than a one-time filtered copy.
Case-sensitive searches and whole-cell matches
SEARCH treats “urgent,” “Urgent,” and “URGENT” as equivalent. If capitalization matters, use FIND in place of SEARCH. FIND is case-sensitive: searching for lowercase “urgent” will not match “URGENT.” The rest of the construction remains the same.
With records in A2:C100, descriptions in B2:B100, and the case-sensitive search text in E1, use:
=FILTER(A2:C100,ISNUMBER(FIND(E1,B2:B100)),"No matches")
A contains-text formula is different from a whole-cell equality test. If the goal is to return only rows where the description is exactly “urgent,” a direct comparison is more appropriate than SEARCH. For whole-cell matching, the include test can be B2:B100=E1. That comparison is not a way to find a phrase embedded within a longer description.
Filter by text plus another condition
You can combine the contains test with other requirements. Multiplying Boolean tests acts like AND: a row is included only when both tests are TRUE. For example, an “urgent” description can be restricted to records whose status is “Open.”
With records in A2:C100, descriptions in B2:B100, and status values in C2:C100, use:
=FILTER(A2:C100,ISNUMBER(SEARCH("urgent",B2:B100))*(C2:C100="Open"),"No matches")
This returns rows that contain “urgent” and have the exact status Open. For an OR test, add the Boolean conditions and check that the sum is greater than zero. The following example returns rows containing either “urgent” or “overdue.”
With descriptions in B2:B100 and the full records in A2:C100, use:
=FILTER(A2:C100,(ISNUMBER(SEARCH("urgent",B2:B100))+ISNUMBER(SEARCH("overdue",B2:B100)))>0,"No matches")
A row containing both terms still appears once: FILTER returns each source row once, even if more than one part of the include test is TRUE. Use parentheses carefully when adding further conditions so it is clear which tests are combined as AND and which as OR.
Blank search cells and wildcard characters
A blank search term can behave unexpectedly. SEARCH with an empty string can return a match position for every text value, making a formula driven by an empty input appear to return all rows. You can guard against that by displaying a prompt until E1 contains a term.
With records in A2:C100, descriptions in B2:B100, and the search input in E1, use:
=IF(E1="","Enter search text",FILTER(A2:C100,ISNUMBER(SEARCH(E1,B2:B100)),"No matches"))
SEARCH also recognizes the wildcard characters asterisk (*) and question mark (?) in its search text. An asterisk represents any number of characters, and a question mark represents one character. A tilde (~) is used to escape a wildcard. If the actual input may contain these characters and you want them treated literally, escaping them before the search avoids accidental wildcard matching.
With records in A2:C100, descriptions in B2:B100, and a literal search term in E1, use:
=FILTER(A2:C100,ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1,"~","~~"),"*","~*"),"?","~?"),B2:B100)),"No matches")
This longer version is usually unnecessary for ordinary words. It is useful when users might search for symbols such as an asterisk in product codes or filenames. The substitutions escape an existing tilde first, then escape the two wildcard characters.
Common errors and practical fixes
A #SPILL! error means Excel cannot place the full result in the cells it needs. Clear any values or merged cells in the spill area, then make sure the formula is not inside an Excel table. The result area can change size whenever the source data or search term changes, so leave room below and to the right of the formula.
A #CALC! error often means there are no matches and no if_empty argument was supplied. Add a message such as “No matches” as the third FILTER argument. If you see #VALUE! or another error, check that the source range and criteria range have matching heights and that the search term is valid.
- Check that the formula uses the column containing the text, not a neighboring status or ID column.
- Make sure the source and criteria ranges begin and end on the same rows.
- Check for a blank search input, especially when the formula refers to a user-entry cell.
- Confirm that cells in the expected spill range are empty and that none are merged.
- Remember that SEARCH ignores case, while FIND does not.
If results should include new records added later, a fixed range such as A2:C100 must be extended when data grows beyond row 100. Converting the source data to an Excel table helps organize expanding records, but the FILTER formula itself should still be placed outside the table because dynamic arrays cannot spill within a table. You can then update the formula’s source references as needed.
Alternatives for older Excel versions
FILTER is not available in Excel 2019 and earlier perpetual versions. For a one-time interactive filter, select the data and choose Data > Filter. Open the dropdown on the text column, then choose Text Filters > Contains and enter the text. This hides nonmatching rows in place rather than producing a separate dynamic result. On Mac, the exact ribbon layout may vary, but the corresponding filtering controls are on the Data tab.
Another option is a helper column that marks matching rows, followed by Excel’s regular filter controls. With descriptions in B2:B100 and the search term in E1, enter this in D2 and fill it down:
=ISNUMBER(SEARCH($E$1,B2))
TRUE marks a row whose description contains the search text; FALSE marks a nonmatch. Turn on the regular Data > Filter controls and filter the helper column to TRUE. This is not a spilling formula, but it works in older versions and makes the test visible beside the source data. As with the dynamic formula, SEARCH is case-insensitive.
Frequently Asked Questions
Does Excel FILTER support a contains-text condition?
Yes. FILTER does not have a special “contains” argument, but you can create one with ISNUMBER(SEARCH(text, range)) and use that result as FILTER’s include condition.
Is SEARCH case-sensitive in Excel?
No. SEARCH ignores letter case. Use FIND when capitalization must match exactly.
Why does my FILTER formula return #CALC!?
Most often, no rows meet the condition and the formula does not specify what to return when the result is empty. Add FILTER’s optional if_empty argument, such as “No matches.”
Can I search for text in multiple columns?
Yes. You can build a condition from searches in more than one column and combine them with addition for OR logic. Make sure each searched column covers the same rows as the data FILTER returns, and decide whether a match in either column or both columns is required.
Can I use this formula in Excel 2019?
Excel 2019 does not include the FILTER function. Use Data > Filter with Text Filters > Contains, or use a SEARCH-based helper column and filter for TRUE.
Related
- Excel DOS Keyboard Shortcuts: What Actually Works
- Excel Direct Access vs Link: What’s the Difference?
- Excel Desktop Keyboard Shortcuts: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LAMBDA Function: Create and Reuse Custom FormulasTo use Excel’s LAMBDA function, define a calculation with named inputs, save it as a workbook name, and…
- Add M to a Number in Excel: Formula and FormattingTo add an M after a number in Excel, apply a custom number format such as #,##0"M" to…
- Excel CONCAT Function Examples: Formulas and Practical TipsExcel’s CONCAT function joins text from cells and typed text into one result, such as a full name,…
- Excel Formula for Number of Years From a Date, ExplainedTo calculate completed years from a date in Excel, use =DATEDIF(A2,TODAY(),"Y"), where A2 contains the starting date. For…
- Excel SUMIFS: Sum Values That Meet Multiple CriteriaTo sum values in Excel when several conditions must be true, use SUMIFS. For example, with regions in…
- Excel Timesheet Formulas for Overtime, Breaks, and PayFor a basic Excel timesheet, put a shift’s start time in B2, end time in C2, and unpaid…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.