How to Use Excel FILTER Formula to Exclude a Specific Value
Question details
The user wants to filter a dataset to include rows matching a specific text pattern while explicitly excluding rows that contain a certain unwanted value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering data from another workbook or sheet based on complex text inclusion and exclusion criteria.
- Observed behavior
- The user needs a formula that successfully returns rows matching a wildcard pattern but rejects specific matches.
Ensure that your spreadsheet software supports Dynamic Array formulas like FILTER (available in Microsoft 365, Excel 2021, or WPS Office). Verify the exact sheet name and data range you are referencing before building the formula.
Combine FILTER with ISNUMBER, SEARCH, and ISERROR
Use a dual-condition logic within the FILTER function to simultaneously search for the desired pattern and reject the unwanted string.
The FILTER function allows multiple criteria by multiplying them together, which acts as an AND logic. By using ISNUMBER(SEARCH()) you can find rows that contain your target pattern. By multiplying this with ISERROR(SEARCH()), you effectively exclude any rows that contain the specific unwanted value.
Click on the top-left cell in a blank area where you want the filtered data to spill over.
Type the formula: =FILTER('SheetName'!A:E, ISNUMBER(SEARCH("15*_A*", 'SheetName'!B:B)) * ISERROR(SEARCH("158_A", 'SheetName'!B:B)))
Replace 'SheetName'!A:E with your actual data range, and 'SheetName'!B:B with the column you are evaluating. Update the search strings to match your specific inclusion and exclusion criteria.
Press Enter. The formula will dynamically return all rows matching the first condition while omitting any rows that trigger the second condition.
Filter Complex Data Easily with WPS Office
WPS Spreadsheet fully supports dynamic array functions like FILTER, allowing you to seamlessly manage, analyze, and extract data using complex inclusion and exclusion criteria.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your dataset.
- 2. Select the output cell: Click the cell where you want the filtered results to appear.
- 3. Input the FILTER formula: Enter your FILTER formula combining ISNUMBER and ISERROR to set your inclusion and exclusion rules.
- 4. Press Enter to view results: Hit Enter to instantly generate the filtered array without altering your original data.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error occurs when the FILTER function finds no rows that meet all your specified criteria. You can add a final argument to the FILTER function, like ,"No results", to display a custom message instead of the error.
Can I exclude multiple specific values using this method?
Yes. You can chain additional exclusion criteria by multiplying more ISERROR(SEARCH("unwanted_value", range)) statements within the include argument of the FILTER function.
Is the SEARCH function case-sensitive?
No, the SEARCH function is not case-sensitive. If you need the exclusion or inclusion to be strictly case-sensitive, use the FIND function instead of SEARCH.
Does this formula work in older versions of Excel?
The FILTER function is a dynamic array function and is only available in Microsoft 365, Excel 2021, and modern spreadsheet software like WPS Office. Older versions will return a #NAME? error.
