In this article, we shall see how to use wildcards in Excel.
Wildcard characters such as the asterisk “*”, question mark “?” and the tilde “~” can greatly extend searching, counting, and additional 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.
How to Use Wildcards in Excel: 4 Easy Methods
Every new parent welcomes the thought of choosing a name for their baby. There are various databases showcasing popular baby name choices in different countries. According to a survey done by Baby Center, 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 search.
The source data is shown below:
1. Use COUNTIF Function to Find Names End with a Specific Word
In this method, I used the COUNTIF function to find names ending with a Specific word “anne”. The COUNTIF Function shows the number of results it finds in the dataset. Look at the second picture of this method. the number 3 indicates the number of results found in the dataset.
Steps:
- 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 “anne“.
- So in Cell E5, we enter the following formula using the asterisk wildcard:
- Press CTRL – ENTER.
- Three names meet the mother’s criteria and preference.
2. Apply Conditional Formatting to Use Wildcards
To use wildcards in Excel, I applied conditional formatting to find a specific name in the dataset. the conditional formatting finds out the specific result by applying new rules. Let’s see the process step by step.
Steps:
- In order to see the actual names that match her criteria, we will use conditional formatting, in combination with wildcards to highlight these names.
- Highlight the range and then go to Home> Styles> Conditional Formatting as shown.
- Click on the drop-down arrow next to Conditional Formatting and choose Highlight Cells Rules> Text that Contains.
- In the Format cells that contain the text: textbox, enter *anne as shown below.
- Click OK to see all the names that end in anne formatted in red as shown below.
- The father likes Isabelle or Isabella, which differ only by one letter, so to find this we do the following.
- 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.
- 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. Thus I have used the question mark wildcard. Click OK.
- Now the program highlighted only Isabelle and Isabella, which differ by one single character.
Read More: Excel Match Wildcard in Lookup Array
3. Find the Names with Asterisks by Using COUNTIF Function
In this particular section of this article, I used the COUNTIF Function to find a particular name with Asterisks. The function will work just like the process described earlier. Here, I will show the whole method step by step.
Steps:
- On the next sheet, we have the preferred names that the grandparents selected, denoted by an asterisk as shown below.
- We can thus count how many names out of the list the grandparents preferred. So in cell D5, we enter the formula:
The tilde, in this case, denotes that the second asterisk is a literal part of the text string. The first asterisk, in this case, is a wildcard and tells Excel to search for any number of characters before the actual asterisk.
- Upon pressing CTRL-ENTER we get 3. So there were three names out of the list that the grandparents preferred.
4. Apply Conditional Formatting to Find the Names with Asterisks
By using Conditional Formatting, I will find the names with asterisks by adding new rules. Let’s see the steps of the method one by one.
Steps:
- 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.
- The grandparents preferred and denoted the three names, using an actual asterisk in the text string shown below
Read More: Search for Question Mark in Excel
Filter Excel Wildcard
In this portion, I will show the process of wildcard filtering. this is an easy process. By filtering the whole dataset, you can find the desired output. let’s see how to filter Excel wildcard.
Steps:
- First Select the whole dataset.
- Go to the Data tab of the Toolbar.
- In the Sort & Filter part, you will find the Filter option. Click on it.
- You will get this icon just at the top right corner of the dataset label. Click on it.
- The window shown in the next picture will be popped up.
- Type the required you want to filter in the search box. Click the OK button.
- At last, you will find the result.
Replace Data with Excel Wildcard
You can find and replace Excel wildcard data with quick and easy steps. Let’s see how to replace data in an Excel wildcard.
Steps:
- First, Press the Ctrl+H buttons. You will find a pop-up window just like the one given below.
- To replace Olivia with Watson, write down the name Olivia in the Find What box.
- Then, write the name Watson in Replace With box.
- After that, Click on the replace button.
- You will find the name Olivia will be replaced with Watson.
Excel Wildcard Not Working
Make sure there are no incorrect characters in your data. Make sure that the data doesn’t contain leading or trailing spaces, uneven use of straight and curly quote marks, nonprinting characters, or other errors while counting text values. COUNTIF might provide an unexpected result in certain circumstances.
Download Practice Workbook
Conclusion
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.
