How to Use Wildcard in FILTER Function in Excel

This guide will explain how to use wildcards in the FILTER function in Excel.

The rules for using the FILTER function in Excel is the following:

• The FILTER function supports both vertical and horizontal arrays.
• When the include argument does not match the dimensions of the array argument, the function will return a #VALUE! error. So make sure the included argument has the same dimensions as the array argument. 
• The FILTER function will return an error when the include array has any errors. 

Excel is a great tool for storing and organizing data. With the use of the different built-in functions and tools, Excel makes it easier to manipulate and look for specific values in a data set.

When it comes to looking for values in a data set, wildcard characters are really helpful. So wildcard characters are special kinds of characters that are used to substitute or represent any other character. If we are unsure of the exact character, we can use a wildcard character in that place. 

Additionally, Excel easily recognizes two commonly used characters as wildcard characters. So the asterisk and the question mark are commonly used wildcard characters in Excel.

However, we cannot use wildcard characters with Excel’s FILTER function. Instead of using wildcard characters, we will use other functions, such as the ISNUMBER function and the SEARCH function. 

Let’s take an example wherein we need to use wildcard characters in the FILTER function.

Suppose we have a data set containing different information about the employees in the company. And you were tasked to filter some employees based on their employee ID. But, you are not sure about the exact characters, so you want to use wildcard characters in filtering.

Since you can’t directly use wildcard characters in the FILTER function, you opted to use other functions to perform the task. 

Great! Before we move on to a real example, let’s first learn the syntax of the FILTER function in Excel.

The Anatomy of the FILTER Function

The syntax or the way we write the FILTER function is as follows:

=FILTER(array, include, [if_empty])

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• FILTER() refers to our FILTER function. And this function is used to filter the selected range or array. 
• array is a required argument. And this refers to the range or array we want to filter.
• include is another required argument. So this refers to an array of booleans where a TRUE value represents a row or column we want to retain. 
• if_empty is an optional argument. And this refers to a value we want to return if no items are retained. 

The Anatomy of the ISNUMBER Function

The syntax or the way we write the ISNUMBER function is as follows:

=ISNUMBER(value)

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we begin any function in Excel.
• ISNUMBER() is our ISNUMBER function. And this function is used to check whether the selected value is a number. If it is a number, it returns TRUE. Otherwise, it will return FALSE.
• value is the only required argument. And this refers to the value we selected that we want to test. So this can be a cell reference, a formula, or a name that refers to the value.

Amazing! Now let’s discuss a real example of using wildcards in the FILTER function in Excel.

A Real Example of Using Wildcard in FILTER Function in Excel

Let’s say we have a data set containing the employee ID, employee name, and department they belong to. And we want to filter the rows where the employee IDs have “NW”. So our initial data set would look like this:

And the employee ID either has an NW which means a new employee or OD or an old employee. Since we cannot use wildcard characters with the FILTER function, we will utilize the ISNUMBER function and SEARCH function. 

Usually, we can simply utilize wildcard characters. So the asterisk is the most common wildcard character, representing any number of characters. For example, we can add an asterisk after letters or text to find data that start with those letters. 

Next, the question mark is another common wildcard character. And this represents any single character. And this helps us make our search more specific. For example, one question mark will match any data containing one character. 

So the FILTER function is used to filter an array or range.  Then, the ISNUMBER function is used to check whether a value is a number. Lastly, the SEARCH function returns the number of characters at which a specific character or text string is first found.

How does the formula work? So this formula will filter the rows we will select for any cells containing what we are looking for. If the function does not find any match, it will simply return a FALSE value or the specific value we inputted to return for a FALSE output. 

In this case, we want to filter the rows containing the character “NW” to get an array of new employees. And our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below. 

How to Use Wildcard in FILTER function in Excel

In this section, we will explain the step-by-step process of how to use wildcard in FILTER function in Excel. To use this method, you can simply follow the steps below.

1. Firstly, we will choose a new location to input the results. Then, we will input the formula “=FILTER(B3:D7, ISNUMBER(SEARCH(“NW”, B3:B7)), “None”)”. Lastly, we will press the Enter key to return the result.

2. And tada! We have successfully used wildcard characters in the FILTER function in Excel.

And that’s pretty much it! We have successfully explained how to use wildcards in the FILTER function in Excel. Now you can apply this learning to your work whenever you need to use wildcard characters together with the FILTER function. 

Are you interested in learning more about what Excel can do? You can now use the FILTER function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe