How To Use Wildcards/Partial Match With Excel’s Filter Function
Filter Function and Partial Matching (Wildcard) Criteria
Unfortunately, as of this writing, the Filter function does not contain an input to perform partial match (wildcard) search criteria. This means you cannot execute common filtering commands such as:
- Begins With
- Ends With
- Contains
- Does Not Contain
While the Filter function appears to be lacking some significant functionality, you can luckily combine other Excel functions to achieve the desired search criteria.
There are 3 inputs in the Filter Function (see below). Throughout this article, we will solely be focusing on the second input since it controls the filtering criteria.
=FILTER(array, includes, [if_empty])Begins With Wildcard Search (Filter Function)
To perform a begins with wildcard search (example = Ash*) you will need to rely on the LEFT Excel function. The LEFT function will allow you to focus on the beginning of each cell you are looking at to filter on.
To determine how many characters you want to isolate, you’ll need to use the LEN Excel function to output the number of characters in your search field.
For our example, we’ll be wanting to filter on names that begin with the three characters “Ash”.
=FILTER($B$4:$D$10,LEFT($B$4:$B$10,LEN(H2))=H2,”No Results!”)
While looking through the formula, you can see that we are feeding the LEFT function a range of cells that encompass the name column in our source data. The formula then determines the length of our search criteria (in this case 3 characters).
This allows the formula to trim each name in the Range B4:B10 to only the first three characters and performs the test of seeing if it matches the search phrase “Ash”. Only the TRUE results are returned, giving us the output shown below.
Since the LEFT function is not case sensitive, any name starting with “ash” would have also returned in the results.
Ends With Wildcard Search (Filter Function)
To perform an ends with wildcard search (example = *en) you will need to rely on the RIGHT Excel function. The RIGHT function will allow you to focus on the end of each cell you are looking at to filter on.
To determine how many characters you want to isolate, you’ll need to use the LEN Excel function to output the number of characters in your search field.
For our example, we’ll be wanting to filter on names that end with the two characters “en”.
=FILTER($B$4:$D$10,RIGHT($B$4:$B$10,LEN(H2))=H2,"No Results!")While looking through the formula, you can see that we are feeding the RIGHT function a range of cells that encompass the name column in our source data. The formula then determines the length of our search criteria (in this case 3 characters).
This allows the formula to trim each name in the Range B4:B10 to only the last two characters and performs the test of seeing if it matches the search phrase “en”. Only the TRUE results are returned, giving us the output shown below.
Since the RIGHT function is not case sensitive, any name ending with “En” would have also returned in the results.
Contains Search (Filter Function)
If you need to perform a filter search for any result that contains a character or phrase (example = *the*), you will need to utilize a combination of two Excel functions to develop a criteria test. There are actually two different functions combination you can use depending on whether you need case sensitivity or not.
Not Case-Sensitive Solution
The SEARCH function allows you to find a phrase within a string of text and returns the character position of where that phrase begins within the text. This function can be used to determine whether a phrase is contained in our data. A numerical value will be returned if the phrase is found and a #VALUE error is returned if the phrase cannot be found.
Since the FILTER function only accepts TRUE or FALSE values for its filtering criteria, we need to be able to convert the SEARCH functions output into TRUE or FALSE values.
This is where the ISNUMBER function comes into play. ISNUMBER performs a test as to whether (you guessed it!) a value is numerical or not. If SEARCH is giving us a character position (number) or #VALUE (not a number), this function easily converts our results into TRUE/FALSE values.
For our example, we’ll be wanting to filter on any name that contains the character “a” without worrying about if it is a capital “A” or a lowercase “a”.
=FILTER($B$4:$D$10,ISNUMBER(SEARCH(H2,$B$4:$B$10)),"No Results!")
Case-Sensitive Solution
The FIND function allows you to find a phrase within a string of text and returns the character position of where that phrase begins within the text. This function can be used to determine whether a phrase is contained in our data. A numerical value will be returned if the phrase is found and a #VALUE error is returned if the phrase cannot be found.
The difference between FIND and SEARCH is simply that FIND is case-sensitive.
Since the FILTER function only accepts TRUE or FALSE values for its filtering criteria, we need to be able to convert the FIND functions output into TRUE or FALSE values.
This is where the ISNUMBER function comes into play. ISNUMBER performs a test as to whether (you guessed it!) a value is numerical or not. If FIND is giving us a character position (number) or #VALUE (not a number), this function easily converts our results into TRUE/FALSE values.
For our example, we’ll be wanting to filter on any name that contains the character “A” while ignoring any lowercase “a” characters.
=FILTER($B$4:$D$10,ISNUMBER(FIND(H2,$B$4:$B$10)),"No Results!")
Does Not Contain Search (Filter Function)
If you need to perform a filter search for any result that does not contain a character or phrase, you will need to utilize a combination of two Excel functions to develop a criteria test. There are actually two different functions combination you can use depending on whether you need case sensitivity or not.
Not Case-Sensitive Solution
The SEARCH function allows you to find a phrase within a string of text and returns the character position of where that phrase begins within the text. This function can be used to determine whether a phrase is or is not contained in our data. A numerical value will be returned if the phrase is found and a #VALUE error is returned if the phrase cannot be found.
Since the FILTER function only accepts TRUE or FALSE values for its filtering criteria, we need to be able to convert the SEARCH functions output into TRUE or FALSE values.
This is where the ISNUMBER function comes into play. ISNUMBER performs a test as to whether (you guessed it!) a value is numerical or not. If SEARCH is giving us a character position (number) or #VALUE (not a number), this function easily converts our results into TRUE/FALSE values.
Since we are looking for a “does not contain” criteria, we will want to ensure we are testing for the FALSE results from the ISNUMBER function. This is why we test to see if it is equal to FALSE.
For our example, we’ll be wanting to filter out any name that contains the character “a” without worrying about if it is a capital “A” or a lowercase “a”.
=FILTER($B$4:$D$10,ISNUMBER(SEARCH(H2,$B$4:$B$10))=FALSE,"No Results!")
Case-Sensitive Solution
The FIND function allows you to find a phrase within a string of text and returns the character position of where that phrase begins within the text. This function can be used to determine whether a phrase is contained in our data. A numerical value will be returned if the phrase is found and a #VALUE error is returned if the phrase cannot be found.
The difference between FIND and SEARCH is simply that FIND is case-sensitive.
Since the FILTER function only accepts TRUE or FALSE values for its filtering criteria, we need to be able to convert the FIND functions output into TRUE or FALSE values.
This is where the ISNUMBER function comes into play. ISNUMBER performs a test as to whether (you guessed it!) a value is numerical or not. If FIND is giving us a character position (number) or #VALUE (not a number), this function easily converts our results into TRUE/FALSE values.
Since we are looking for a “does not contain” criteria, we will want to ensure we are testing for the FALSE results from the ISNUMBER function. This is why we test to see if it is equal to FALSE.
For our example, we’ll be wanting to filter out any name that contains the character “A” while ignoring any lowercase “a” characters.
=FILTER($B$4:$D$10,ISNUMBER(FIND(H2,$B$4:$B$10))=FALSE,"No Results!")
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
I Hope This Microsoft Excel Article Helped!
Hopefully, I was able to explain how you can use a combination of Excel functions with the Filter function to carry out a bunch of different wildcard or partial match search criteria. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.