Wildcards in Formula
A wildcard character is a character that can be used as a substitute for another character or characters.
I use wildcards in formula mostly in reference or look-up formulas like COUNTIF or SUMIF for example.
The wildcards can be used to look up words beginning with or ending with criteria.
Note : It can be difficult to see the asterisk clearly in this post, I have highlighted them in red font – *
Wildcards In Formula Example
Using the example below, we will use wildcards in formula.
Column A lists 16 tests and column B lists the results of those tests.
Because humans are humans and don’t always follow the same standards, some people have logged “Pass” results as “Pass” or “Passed” and “Fail” results have been recorded as “Fail”, “Failed” or “Failure”.
Simple COUNTIF
The original plan was to use COUNTIF formula to summarize the results and show how many passed and how many failed, but using the simple COUNTIF formula we don’t get the results we wanted
- Our COUNTIF range is from B2 to B16, this is where we are going to look for our criteria and count the occurences.
- Our criteria is the header in E1 for the “Pass” section and F1 for the “Fail” section.
Our results are 1 and 5 for Pass and Fail respectively. This is because those words only appear 1 and 5 times. They are included in longer words though.
In our example above it is easy to just replace all the alternative versions of Pass and Fail with the words Pass and Fail, or manually count how many of each there are. There are only 15 test results after all. But in reality, there could be hundreds or thousands of results. We may not be able to just change all the alternative versions of the words to just Pass and Fail – For example it might not be a simple “Pass”, the tester may want to record “Passed but with exception”, you don’t want to override this with just “Pass”.
This is where wildcards in formula are useful.
Wildcards in Formula – “Beginning With”
All our results begin with “Pass” or “Fail” but don’t necessarily end with those words.
We will use the asterisk ( * ) as a wildcard to replace any characters that might come after Pass or Fail.
In the case of the formula in the “Pass” section, our criteria is going to be “Pass*” and this will query for any string beginning with Pass. In the Fail section our criteria will be “Fail*“.
Now we can see that there were 6 passes and 9 fails, which is the result we are looking for.
You don’t have to enter your criteria as “Pass*” and “Fail*” – you could query the header in row 1 and add * to it.
Wildcards In Formula – “Ending With”
We have seen that by adding the wildcard * to the end of a string we could query text beginning with criteria.
By putting the wildcard at the start of the criteria, we can query for strings ending with text.
Here we will query our results to count how many end with the letter “d”
7 of our results end with a D – 5 Passed and 2 Failed.
By putting the wildcard at the start of the criteria, we are basically querying some characters and then “d” – or anything ending with d.
Wildcards In Formula – “Containing”
Putting wildcards in formula at the start of a string will query anything ending with that string.
Putting the wildcards in formula at the end of a string will query anything beginning with that string.
Now, if you put the wildcards in formula at both the beginning and end of a string will query anything containing that string.
All our results contain the letter “a” – so for demonstration purposes I will change my COUNTIF criteria to be “*a*” which should result in 15, as all 15 of our results contain the letter a.
Other uses of Wildcards in Excel
Wildcards are not limited to formulas.
In VBA I use them when I have to apply text filters through code for filtering text beginning with, ending with or containing certain text.
