Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

Excel Wildcard characters for Advanced Search and Partial Match

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.

Wondering!! What are you searching for?
  • 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.

Download Your Example Excel File

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.

Return count of text that is alike as per given Wildcard criteria
Return count of text that is alike as per given Wildcard criteria
Find TextResultComment
H0*0Count only those words that have initials of ‘HO‘ – Returns Zero 
*Y2Words that end with ‘Y
H????2Count words that start with ‘H‘ + 4 characters 
?????2Count words with only 5 character letter
*~?1Count number of words with wildcards i.e. How? Why*.
day1Use 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.

Lookup for the partial match and return data
Lookup for the partial match and return data

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.

Use Wildcards in filter and find related texts
Use Wildcards in filter and find related texts
Use Wildcards within Find and Replace dialogue box
Use Wildcards within Find and Replace dialogue box