Excel, as a program, is used by many users for ease of data analysis and documentation, which is done in columns and rows. On the other hand, Wildcards are very special because they are characters that replace other characters in Excel formulas. The wildcards are very useful in excel because they help show the text strings that share the same pattern.
They are very good with texts, and they can never be used for numbers. If you have similar cell content you don't know, wildcards come in handy to differentiate them. There are three wildcard characters in excel, which are the asterisk (*), question mark (?), and tilde. The asterisk represents any number of characters. The question mark works for one single character representation. On the other hand, the tilde character represents both the asterisk and question mark as they appear.
The three characters are used in some of the basic excel formulas, e.g., SEARCH, VLOOKUP, FIND AND REPLACE, COUNTIF, COUNTIFS CONDITIONAL FORMATTING, among others. This article will look at how the three are used in excel. The image below shows you where to locate the conditional formatting tool.
1. Use of wildcards in VLOOKUP
VLOOKUP normally looks for specific values in a list, but it brings results of any corresponding value. The wildcards become helpful in this formula to help it narrow down to the specific value. A good example below is a list of countries where you want to find out the number of deliveries done for Nairobi. The list, however, lists Nairobi, Kenya, instead of just Kenya
The wildcard asterisk (*) is what will be used in your VLOOKUP formula to determine the number of deliveries done to Nairobi. To get the information you need, key in =VLOOKUP (D3&"*"; A1:B9; 2; FALSE).
The use of this wildcard instructs you to look for any texts that begin or end with the lookup value in D3-Kenya, and it may have additional characters after it.
3. Use of wildcards in COUNTIF
The wildcard used in this formula is the question mark, and it helps to find out the number of cells in a certain range to meet a specific number of characters. For example, the following is a list of students' country codes, and the quest is to acquire the number of students' country codes with three characters in their respective country codes.
When using the question mark wildcard, you need to understand that each (?) represents a character. In this scenario, since we need to look for a country code with three characters, we need to include three (???).
To get your answer, key in; =COUNTIF (A2:A9;"???") Once that is done, you will get your number.
It is fundamental to note that most of the wildcards characters are very effective if the data you are using is incomplete or partially available. As shown in the examples above, the wildcards have replaced single characters in the formulas. The best thing is that they are used to search, match and bring back a partial match in formulas or problems with incomplete lookup. Do not forget that they are only used with texts and not numbers.