How to Use Wildcards in Excel?
Wildcard characters such as the asterisk “*”, question mark “?” and the tilde “~” can greatly extend searching, counting, and addition functionality in Excel. Excel wildcards can be used with functions such as AVERAGEIF, SUMIF, COUNTIF, Excel database functions and others.
The asterisk “*” is used to substitute any number of characters, whereas the question mark “?” is used to represent or substitute one character. The tilde “~” is used to identify a literal question mark or asterisk character in the text string itself.
What do I mean by all of this? Well, let’s get started with a simple example.
Every new parent welcomes the thought of choosing a name for their baby. There are various databases showcasing the popular baby name choices in different countries. According to a survey done by Babycenter, which surveyed nearly 400,000 parents, the most popular baby name choice for girls in 2016 was Sophia, whereas the most popular baby name choice for boys in 2016, was Jackson.
In our example, two hypothetical expectant parents have compiled a list of baby names based on their own preferences, and input received from friends and relatives. They are expecting a baby girl. It is getting closer to delivery time, so they have decided to go through their compiled list of preferred names and use wildcards in order to enhance their searching.
The source data is shown below:
1) The mother likes the sound of names that end in anne such as Joanne and wants to see how many are on the list currently. So the asterisk wildcard can be used in this case since she is not exactly sure how many other characters are in front of the anne.
2) So in Cell D5, we enter the following formula using the asterisk wildcard:
=COUNTIF(A5:A22,”*anne”) and press CTRL – ENTER.
3) Three names meet the mother’s criteria and preference.
4) In order to see the actual names that match her criteria, we will use conditional formatting, in combination with wildcards to highlight these names.
5) Highlight the range and then go to Home> Styles> Conditional Formatting as shown.
6) Click on the Drop-down arrow next to Conditional Formatting and choose Highlight Cells Rules> Text that Contains.
7) In the Format cells that contain the text: textbox, enter *anne as shown below.
8) Click OK to see all the names that end in anne formatted in red as shown below.
9) The father likes Isabelle or Isabella, which differ only by one letter, so to find this we do the following.
10) Firstly, we clear the current conditional formatting by choosing Home> Styles> Conditional Formatting> Clear Rules> Clear Rules From Entire Sheet as shown below, in order to remove the prior conditional formatting.
11) Highlight the range, again. Go to Home> Styles> Conditional Formatting> Highlight Cells Rules> Text that Contains and enter Isabell? in order to see Isabelle and Isabella highlighted. They differ by only a single character and thus the question mark wildcard is used. Click OK.
12) Now only Isabelle and Isabella, which differ by one single character is highlighted.
13) On the next sheet, we have the preferred names that the grandparents selected, denoted by an asterisk as shown below.
14) We can thus count how many names out of the list the grandparents preferred. So in cell C5, we enter the formula:
The tilde, in this case, is denoting that the second asterisk is a literal part of the text string. The first asterisk, in this case, is a wildcard and is telling Excel to search for any number of characters before the actual asterisk.
15) Upon pressing CTRL-ENTER we get 3. So there were three names out of the list that the grandparents preferred.
16) In order to see the actual names using conditional formatting, we highlight the range again. Go to Home>Styles>Conditional Formatting>Highlight Cells Rules>Text that Contains and enter *~* and then Click OK.
17) The three names the grandparents preferred and denoted, using an actual asterisk in the text string is shown below
Wildcards can greatly enhance Excel functions. They allow for partial match searching as well as partial criteria fulfillment.
Please feel free to comment and tell us how you use wildcards, which wildcard combinations have enhanced your searching and baby name preferences in your region or country.