In this article, you will learn how to use a wildcard in Excel!
Sometimes you come across various situations where you have a huge set of data in Excel and you don’t remember the entire phrase you want to search for and you remember only a part of it and you wish you could somehow get to that cell. There might be situations where you would like to tweak some character or text related functions to behave in a certain way to match only a part of the phrase or a word, but you always wondered how to do this in Excel. Well, In these case, Wildcards come in handy!!
Let’s see how we can use Wildcards. The two wildcards allowed in Excel are: “*” (asterisk – used to substitute multiple characters) and “?” (question mark – used to substitute one character). There is another wildcard which helps in searching for an asterisk or a question mark and that is the tilde “~”. Let’s take an example and understand how to use these. Please use the following file to follow along with the examples which we look at.
Please use the following file to follow along with the examples which we look at:
Example 1: Usage of wildcard “*” (asterisk)
- We’ll take the data in the columns A, B and C (i.e. the list of Top 100 most populated cities in the USA and their population) and try to fill in the required details in column H and column I
- The city column in Column B isn’t just names of the cities but the name of the city along with the state. It is in the format “City”, “State” as represented in the snapshot below
- Since we need to collate the information at the state level, we’ll use the wildcards to find a match for the state(In column G) from Column B, irrespective of the cities involved
- In Column H, we’ll write a formula for each state to get the count of cities that are present in the Top 100 largest cities and in column I, we’ll write a formula for each state to get the total population of the cities from that state that are present in the Top 100 largest cities
- For Column H, we shall use COUNTIF and for Column I, we shall use SUMIF where the wildcard “*” will be used in the criteria section of the formula
- The formula in H2 would be as follows, this counts the values in cells B2:B101 when the name in the cell ends with the state name in G2. For this purpose, we are using the “*” symbol
- The formula in I2 would be as follows, this sums the values in cells C2:C101 when the name in the range B2:B101 ends with the state name in G2. For this purpose, we are using the “*” symbol
- Then you can drag the formulas in H2 & I2 until H51 & I51 respectively. You’ll notice that California and Texas are the states with higher number of cities from the given list with 17 and 13 cities respectively
Also, this wildcard can be used if we need to search for a word having a certain suffix instead of a prefix, then we would add the asterisk after. Eg. “California*”. Another way we can use the asterisk before and after a word. E.g: “*California*” to count a word regardless of its position.
This concludes the usage of the asterisk wildcard character “*”
Example 2: Usage of wildcard “?” (question mark)
- For this example, we’ll consider the data in column K
- As you can observe we just have two city names written differently. Like we discussed earlier, the wildcard character question mark (?) can be used in replacement to single character
- Let’s say we would like to count the number of appearances of New York irrespective of the way it is represented, i.e. ignoring the character present between New and York
- To achieve this, we’ll write the formula shown below in cell M2. This counts all the occurrences of New York ignoring the single character in between
- We get the count as 3
- Let’s repeat the same with San Antonio by writing the formula shown below in cell M3. This counts all the occurrences of San Antonio ignoring the single character in between, note that the occurrence SanAntonio without any character between San and Antonio is ignored.
- We get the count as 2
- This concludes the usage of wildcard character “?”
Example 3: Usage of wildcard “~” (tilde)
- You can use the tilde wildcard character can when you would like to search or match the actual “*” or a “?” instead of treating them as wildcard character
- You think of it as the wildcard for wildcard characters!
- Don’t worry if the above point is confusing, you’ll understand about it using the example we are considering
- We know if we use a “*” it replaces multiple characters and “?” replaces a single character when used in the search condition. What if we want to search for an actual “*” or a “?”. We use tilde in such cases
- If you closely notice in Column B, you realize that there is an asterisk (*) where the state name and the city names are same
- If we want to count such cases, we’ll use the tilde (~) wildcard along with the asterisk (*)
- Write the following formula in K10
- This formula counts all the occurrences where the cell strings begin with an asterisk (*), in the condition of the above formula, tilde (~) followed by first asterisk (*) searches for the actual asterisk in the string and the 2nd asterisk is treated as a wildcard character to replace any number of characters
- The result of the above formula will be 2, there are only two such occurrences, one is New York and the other is Washington
This way you can use wildcards to do partial matches with strings or phrases. You can use it in the following situations:
- Places where there is a condition involving a string
- In find and replace
- In filters
Finally, this completes everything related to wildcards in Excel. You can refer to the following file for final results and formulas.
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018