Wildcards in Excel: how it works
With * you look for zero or more characters and with ? for exactly one. "UK*" finds everything that starts with UK.
Try the first exercises for freeIn the lesson Wildcards: * and ?
How do wildcards work?
In a condition, a wildcard stands for characters you don't know or that don't matter. The asterisk * stands for zero, one or more characters. The question mark ? stands for exactly one character.
"UK*" finds everything that starts with UK, "*laptop*" everything that contains laptop, and "P-1??" every code that starts with P-1, followed by two characters. Wildcards work in the conditions of COUNTIF, SUMIF, VLOOKUP with FALSE and SEARCH, among others.
When do you use wildcards?
- All codes that start with UK: =COUNTIF(A2:A100,"UK*").
- Every product name that contains laptop: =SUMIF(B2:B100,"*laptop*",C2:C100).
- Codes with a fixed length, such as P-1 and two more characters: =COUNTIF(A2:A100,"P-1??").
- Looking up a name when you only know how it starts: =VLOOKUP("Walk*",A2:C50,2,FALSE).
Step by step
C1 contains =COUNTIF(A2:A4,"UK*"). Counts the numbers that start with UK. Change the numbers or the formula and see what happens.
This is how to go about it:
- In C1, type a function that takes a condition, here COUNTIF, and select the numbers.
=COUNTIF(A2:A4, - Type the fixed start in quotation marks, with an asterisk after it for the rest.
=COUNTIF(A2:A4,"UK*" - Close the bracket and press Enter: two numbers start with UK.
=COUNTIF(A2:A4,"UK*")
More examples
A word anywhere in the text
D1 contains =SUMIF(A2:A4,"*laptop*",B2:B4). With an asterisk on both sides, every row that contains laptop counts, at the start or at the end.
Exactly one character
C1 contains =COUNTIF(A2:A5,"P-1??"). Each question mark stands for exactly one character: P-1020 is too long and P-210 starts differently.
Looking for a real asterisk
C1 contains =COUNTIF(A2:A4,"*~*"). With a tilde before the asterisk, Excel looks for a real asterisk, here at the end of the text.
Where do they work?
In COUNTIF, SUMIF, VLOOKUP with FALSE, SEARCH and more. If you're really looking for an asterisk, put a tilde in front of it: ~*.
Where it often goes wrong
Practise this in the lesson Wildcards: * and ?. The first exercises are free.
Frequently asked questions
How do I check whether a text contains a word?
Put an asterisk on both sides: "*word*".