How to Use Excel FILTER with Wildcard (2024 Guide)

In this article, we will show you how to use a wildcard in an Excel filter. Simply follow the steps below!

How to Use the FILTER Function in Excel with a Wildcard

You can use the FILTER function in Excel with a wildcard character to filter data based on partial matches. Excel supports wildcard characters like “?” (which matches any single character) and “*” (which matches any sequence of characters).

Here’s how to do it:

1. Open Excel and Set Up Your Data

Suppose you have a dataset where column A contains names and column B contains their corresponding job titles. You want to filter all names where the job title contains the word “Manager”.

2. Choose a Cell for the Output

Click on a cell where you want the filtered results to appear. This could be in a new column or on a new sheet, depending on how much space the results may need.

3. Enter the FILTER Formula with Wildcard

Click on the cell where you want the output and type the following formula:

=FILTER(A:A, ISNUMBER(SEARCH("*Manager*", B:B)))

SEARCH(“*Manager*”, B:B): Searches for the string “Manager” anywhere in each cell of Column B. It returns the position number of “Manager” within the text if found, or an error if not found.

ISNUMBER(…): This checks whether the result of the “SEARCH” function is a number (indicating that “Manager” was found).

FILTER(A:A, …): Filters the names in Column A based on whether their corresponding job titles in Column B contain “Manager”.

For this example, we chose cell D2 as our output cell. Here’s what the formula looks like:

4. Press Enter and View the Results

After entering the formula, press Enter. If you’re using Excel with dynamic arrays (Excel 365 or Excel 2019), the results will automatically “spill” into the cells below your initial output cell.

The output will be the list of names whose job titles contain “Manager”.

You can modify the wildcard pattern to fit different requirements. For example, if you want to find job titles starting with “Manager”, use “Manager*”. If you need to match job titles that end with “Manager”, use “*Manager”.

If no match is found, Excel might show a #CALC! error because the FILTER function cannot find any entries that match the condition.

You can handle this by adding an optional argument to FILTER for a friendly message or alternative result, like so:

=FILTER(A:A, ISNUMBER(SEARCH("*Manager*", B:B)), "No managers found")

Using our example and removing the word “Manager” from the job titles:

We hope that you now have a better understanding of how to use the Excel FILTER with a wildcard. If you enjoyed this article, you might also like our articles on how to save a filtered view in Excel and how to filter strikethrough in Excel.