In this article, You will learn the essential aspect of Excel wildcard characters like ‘?,*,~’ for advanced search and partial match using three separate examples.
In many occasions, you are not very sure about the exact term what you are looking for or looking to find a group of similar characters, if we use wildcard that gives us enough flexibility to returns the results that are alike.
There are very few people who work on Excel know the power of using Wildcard characters for advanced search.
- While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.
Wildcards can give you lots of option to search for a different kind of texts and numbers starting or ending with one or multiple characters.
There are three kinds of Wildcard characters in Excel:
Asterisk (*) | To match any number of characters |
Question Mark (?) | To match any single character |
Tilde (~) | Search special characters |
Wildcard to count similar words
Let’s start with our first example where we will be using COUNTIF function to return a count of those letters that fulfill our search criteria using wildcards.
In this example, you have three texts on which you will apply different search criteria using wildcards, and returns the counts of matching results.
The COUNTIF function returns a count of number based on given criteria like =COUNTIF(A1:A5,”>5″) that search numbers in the list >5 and return the count of numbers that are greater than 5.
| Find Text | Result | Comment |
| H0* | 0 | Count only those words that have initials of ‘HO‘ – Returns Zero |
| *Y | 2 | Words that end with ‘Y‘ |
| H???? | 2 | Count words that start with ‘H‘ + 4 characters |
| ????? | 2 | Count words with only 5 character letter |
| *~? | 1 | Count number of words with wildcards i.e. How? Why*. |
| day | 1 | Use ampersand – ‘&’ to create a dynamic formula with wildcards *day* returns count of words that include ‘day’ text. |
Partial match using Wildcard and Vlookup
Wildcard can be very helpful in scenarios where you don’t know or want to enter the full name of a text you are searching and also want to find the related data based on your incomplete information.
So in our next example dataset, we have two variables i.e. city with state and respecting zip code of the city. Our task is to find the respective Zip code using the partial text of the city name.
In such cases, a wildcard can be an excellent and easy option along with the Excel VLOOKUP function to look up the respective value of a data set.
The VLOOKUP function extract data in a vertical pattern which means heading in 1st column and value begins from the 2nd column and retrieve data from left to the right.
Wildcard with Excel Filter
You can also use Wildcards with Excel Filter or with Find and Replace feature directly in its search box. Excel understands each wildcard and returns the matching value.
You can use the asterisk (*) to represent any sequence of characters or the question mark (?) to represent any single character.
