Wildcards in Excel: match partial text with * and ?
In Excel, * matches any number of characters and ? matches exactly one. =COUNTIF(A2:A7,"*a*") counts names containing an a. Wildcards work in COUNTIF, SUMIF, COUNTIFS, SUMIFS, VLOOKUP and MATCH.
The examples use the same sales table as the practice box below.
| A Rep | B Sales | E Region | H Hired | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | East | 3/12/2018 |
| 3 | Luis | 830 | West | 7/1/2021 |
| 4 | Priya | 2,100 | East | 11/20/2016 |
| 5 | Marcus | 475 | North | 2/15/2023 |
| 6 | Jen | 990 | West | 9/3/2019 |
| 7 | Omar | 1,600 | East | 1/27/2020 |
The three symbols
| Symbol | Means | Example |
|---|---|---|
* | Any number of characters, including none | "J*" matches Jen |
? | Exactly one character | "?uis" matches Luis |
~ | Treat the next * or ? as a real character | "~*" matches an asterisk |
Examples
=COUNTIF(A2:A7,"*a*")returns 4: Dana, Priya, Marcus and Omar.=COUNTIF(A2:A7,"?????")returns 1: Priya, the only five-letter name.=SUMIF(A2:A7,"M*",B2:B7)returns 475, the sales for names starting with M.=VLOOKUP("No*",J2:L4,2,FALSE)returns Cara Lim, the manager for North.
Using a cell with a wildcard
Join the wildcard to a cell: =COUNTIF(A2:A7,"*"&C1&"*") counts names containing whatever is typed in C1. This is how simple search boxes work in spreadsheets.
Where wildcards don't work
- In a plain comparison.
=IF(A2="J*","Yes","No")looks for the actual text "J*". Use=IF(COUNTIF(A2,"J*"),"Yes","No")instead. - In XLOOKUP, unless you set match_mode to 2.
- On numbers.
"12*"won't match 1250, since wildcards only match text.
Try it: Count names with an a
In B8, count how many rep names in A2:A7 contain the letter a anywhere.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Are wildcards case sensitive?
No. "j*" and "J*" match the same names.
Does Google Sheets support wildcards?
Yes, in COUNTIF, SUMIF, VLOOKUP and MATCH, the same as Excel.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
How to use COUNTIFS in Excel to count rows that meet several conditions, including number ranges, dates and OR logic, with a live exercise.
How VLOOKUP works in Excel, why you should always end it with FALSE, how to lock the table with $, its limits, and a live exercise.