How to Exclude Rows Containing Specific Text Using Excel FILTER
Question details
The user needs to filter out rows containing specific text (like 'Done') in a dataset using the FILTER function, while avoiding #VALUE! errors.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying complex filtering criteria in a spreadsheet where rows containing specific keywords must be excluded without triggering #VALUE! errors.
- Observed behavior
- Using the SEARCH function inside FILTER causes a #VALUE! error when the target text is not found, breaking the entire array filtering logic.
Ensure your spreadsheet software supports dynamic array functions like FILTER. Verify that your dataset is organized in a structured table or defined range without merged cells.
Use ISERROR and SEARCH with the FILTER Function
Wrap the SEARCH function in ISERROR to handle cases where the text is not found, returning TRUE for rows to keep.
When filtering rows to exclude specific text, the SEARCH function looks for the keyword. If the keyword is not found, it naturally returns a #VALUE! error, which breaks the FILTER function. Wrapping SEARCH inside ISERROR converts this error into a TRUE/FALSE logical statement that FILTER can process safely.
Click on an empty cell where you want the top-left corner of the filtered array to appear.
Type the beginning of your formula: =FILTER(Duplicates,
Use the ISERROR and SEARCH combination to define the text you want to exclude. For example: ISERROR(SEARCH("Done", Duplicates[Notes]))
If you have other criteria, combine them using the multiplication operator (*) for AND logic: =FILTER(Duplicates, condition1 * condition2 * ISERROR(SEARCH("Done", Duplicates[Notes])))
Press Enter to generate the filtered array. The rows containing the word "Done" will be excluded.
Use REGEXTEST to Exclude Multiple Keywords
For newer versions of Excel, use the REGEXTEST function to easily check for one or multiple keywords using regular expressions.
Filter Complex Data Arrays Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like FILTER, ISERROR, and SEARCH, allowing you to exclude specific text and handle complex datasets effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data.
- 2. Enter the FILTER formula: Select a blank cell and input the combination formula: =FILTER(A2:C10, ISERROR(SEARCH("ExcludeWord", B2:B10)))
- 3. Generate results: Press Enter to instantly populate your filtered data without the excluded keywords.
Frequently Asked Questions
Why does the SEARCH function return a #VALUE! error in FILTER?
The SEARCH function evaluates each cell for the specified text. If the text is missing, it returns a #VALUE! error instead of a logical FALSE. Because the FILTER function cannot process arrays containing errors, wrapping SEARCH in ISERROR translates the error into a TRUE result, meaning 'text not found', which keeps the row in the filtered results.
Can I exclude multiple different words using ISERROR and SEARCH?
Yes, you can exclude multiple specific words by multiplying several ISERROR statements together within the FILTER function. For example: =FILTER(Data, ISERROR(SEARCH("Word1", Range)) * ISERROR(SEARCH("Word2", Range))).
Is the SEARCH function case-sensitive?
No, the SEARCH function is not case-sensitive. If you need a strict, case-sensitive search to exclude text, use the FIND function instead, wrapped in ISERROR exactly the same way.
What if I want to include rows containing specific text instead of excluding them?
To include rows with specific text, use the ISNUMBER function instead of ISERROR. The formula changes to: =FILTER(Data, ISNUMBER(SEARCH("TargetText", Range))).
