Excel functions / References & tools
Wildcard characters
Match part of some text with *, ? and ~ in COUNTIF, SUMIF, lookups and Find.
At a glance
* | Any number of characters, including none. "*tea" matches Green tea and Mint tea. |
|---|---|
? | Exactly one character. "?at" matches cat and hat, but not that. |
~ | Treats the next * or ? as a real character. "~*" finds an actual asterisk. |
How it works
Wildcards let a condition match text you only partly know. They work in COUNTIF, SUMIF, AVERAGEIF and their -IFS versions, in MATCH, VLOOKUP and XLOOKUP (with match mode 2), and in the Find and Replace box.
Combine them with a cell by joining with &: ="*"&E1&"*" matches any text containing whatever is typed in E1.
Example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Amount | ||
| 2 | Green tea | 10 | 3 | |
| 3 | Coffee | 30 | ||
| 4 | Mint tea | 24 | ||
| 5 | Black tea | 36 | ||
| 6 |
Result: 3. Three of the products end in tea.
Tips
- Wildcards only work with text. They do not match parts of numbers.
Practise Wildcard characters
Try it yourself in a real spreadsheet, with instant feedback on every answer.