You can use the following syntax to use wildcard characters within a FILTER function in Excel:
=FILTER(A2:B12, ISNUMBER(SEARCH("some_string", A2:A12)), "None")
This particular formula will filter the rows in the range A2:B12 where the cells in the range A2:A12 contain “some_string” anywhere in the cell.
If no cell contains “some_string” then the formula will simply return the output “None.”
The following example shows how to use this syntax in practice.
Example: Use Wildcard in FILTER Function in Excel
Suppose we have the following dataset in Excel that contains information about various basketball teams:
We can use the following formula to filter for the rows where the Team column contains “ets” anywhere in the cell:
=FILTER(A2:B12, ISNUMBER(SEARCH("ets", A2:A12)), "None")
The following screenshot shows how to use this formula in practice:
We can see that the filter only returns the four rows that contain “ets” somewhere in the Team name:
- Nets
- Nuggets
- Hornets
- Rockets
Note that if we filter using some string that doesn’t exist in any Team name, the formula will simply return the output “None.”
For example, suppose we attempt to filter for rows where the Team name contains “zzz” somewhere in the cell:
The formula returns “None” because no Team name has “zzz” anywhere in the Team name.
Note: The SEARCH function in the formula performs a case-insensitive search. If you’d like to perform a case-sensitive search, replace SEARCH with FIND in the formula.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Delete Rows with Specific Text
Excel: How to Check if Cell Contains Partial Text
Excel: How to Check if Cell Contains Text from List