Learning Excel: Wildcards
Wildcards
"How many dresses did we sell?" The labels say Dress green, Dress black… None of them is called just Dress. Time for wildcards.
The asterisk
In a criterion, * stands for "any characters, or none at all". "Dress*" matches anything that starts with Dress.
=COUNTIF(A2:A16,"Dress*") finds three items: Dress green, Dressing gown and Dress black. The dressing gown too, because anything may come after Dress.
=COUNTIF(A2:A16,"Dress*")
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | |||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | 3 | ||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | ||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | ||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | ||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | ||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | ||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | ||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | ||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | ||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | ||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | ||||
| 13 | Vase | Household | £7.00 | Sat | Grace | ||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | ||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | ||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | ||||
| 17 |
The question mark and the tilde
? stands for exactly one character. "T-shirt ?" matches T-shirt S and T-shirt M, but not T-shirt XL: that's two characters.
Looking for a real question mark or asterisk? Put a tilde in front of it: "What~?*" finds "What? Game".
Try it yourself
How many items have something to do with books? In I2, count every item with "book" in it, anywhere.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | ||||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | ||||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | |||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | |||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | |||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | |||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | |||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | |||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | |||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | |||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | |||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | |||||
| 13 | Vase | Household | £7.00 | Sat | Grace | |||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | |||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | |||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | |||||
| 17 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson