It’s not every time you want to search for something in Excel that you have the full string in mind. Maybe you only remember part of a name or a few characters. Scrolling through endless rows and columns, trying to spot that one vague entry, can feel like looking for a needle in a haystack, and I’m sure there are better ways you’d rather spend your time.
Thankfully, Excel’s wildcard characters can help you get there faster. Once you learn how to use them, you’ll find that they fit naturally into features like filters, the Find and Replace tool, and formulas using COUNTIF, VLOOKUP, SEARCH, and other smart Excel conditional functions. Most importantly, they make your searches faster, smarter, and far more efficient.
The asterisk wildcard (*)
When you need to match any sequence, no matter how long or messy it is
The asterisk (*) is the wildcard character that represents any sequence of characters, including when there are no characters at all. It’s the most flexible of the bunch, since it can replace any number of characters in your search. You can use it to find text that starts with, ends with, or simply contains a specific string.
For example, when using the Find and Replace feature in Excel to search for different product tags, you can place the asterisk at the beginning, middle, or end of the string you do know:
*sheet
Bed*
B*t
You can use the same idea with Excel’s Filter feature. Suppose you have a long list of employees and want to display only those whose job title includes the word “Manager,” regardless of what comes before or after it, such as Regional Manager or Manager II. In the filter box for the Job Title column, you can select Text Filters -> Contains, and enter the criteria *Manager*. Excel will then display every title that contains the word, saving you from a lot of manual sorting.
You can also use the asterisk directly in formulas for partial matches. For instance, if you want to look up the name of your sales rep based on a partial city match, you could use a formula like this:
=FILTER(C2:C30, ISNUMBER(SEARCH("Lagos*", B2:B30)))
Adding a space before the wildcard would narrow the results even further, returning only Mark King, whose location is “Lagos Region, Nigeria”:
=FILTER(C2:C30, ISNUMBER(SEARCH("Lagos *", B2:B30)))
In short, the asterisk tells Excel to search for any text that fits around your known pattern. Beyond FILTER, it also works with other functions that use criteria or pattern matching, such as COUNTIF, MATCH, and AVERAGEIF.
The question mark wildcard (?)
For matching text when only a few characters vary
This wildcard represents any single character. It acts as a placeholder for exactly one character in a text string, making it especially useful when you’re working with fixed-length data or spotting inconsistencies.
I often use it when troubleshooting or counting strings that should follow a specific format or length. For instance, in Excel’s COUNTIF function, you can use a certain number of question marks to count how many cells have that exact number of characters. If you have a column of invoice IDs and want to count how many are exactly six characters long, you could use a formula like this:
=COUNTIF(A2:A6, "??????")
Excel counts every character, including punctuation. Therefore, an invoice like INV-002 won’t be counted as six characters because the hyphen is also treated as a character.
You can use the question mark wildcard in nearly all the same Excel functions and features that support the asterisk. The difference is just that the question mark matches exactly one character, while the asterisk can represent any number. For example, if you were using Find and Replace to search for “p??ce,” Excel would find both “piece” and “peace.”
This wildcard also proves useful in conditional formatting. Suppose you have a list of product codes in cells D2:D6, each meant to be exactly five characters long (like CODE1). You could use this formula to flag any entries that aren’t the right length:
=COUNTIF(D2,"?????")=0
If a cell’s content matches the criteria “?????,” the COUNTIF function returns TRUE (or 1). If it doesn’t, because the code is too long or too short, it returns FALSE (or 0).
To apply this visually, select your range of cells, go to Home -> Conditional Formatting -> New Rule…, and choose Use a formula to determine which cells to format. Enter the formula above, then click Format… to select a fill color (such as red) for cells that fail the rule. Once you click OK, Excel will highlight any product codes that aren’t exactly five characters long.
It’s a quick and reliable way to validate your data, and one of the most practical uses of the question mark wildcard.
The tilde wildcard (~)
For the few times you actually need to search for * or ?
In Excel, the asterisk and question mark are usually treated as wildcard characters. But if you want Excel to treat them literally, as the actual characters themselves rather than as wildcards, you need to place a tilde before them. Now, you might be wondering in what situations you’d need Excel to treat the wildcards literally. Well, those exist.
Suppose you have a list of IDs where some include an asterisk in the middle to separate sections (for example, P*Laos*45 or S*Bahrain*05), indicating whether it’s a product (P) or a service (S), along with its location and order unit. If you want to count only the products ordered in Laos, you can make Excel look for the literal asterisk by using a formula like this:
=COUNTIF(A1:A43, "P~*Laos*")
This formula instructs Excel to treat the first asterisk as a regular character and use the final one as a wildcard to capture any text after “Laos.” If you leave out that final asterisk, Excel will only look for an exact match of “P*Laos” and ignore the rest.
The same idea applies to the question mark wildcard. Imagine you’re working with survey responses and want to find every instance of a literal question mark (?) in your sheet, perhaps to quickly review the questions participants entered. In the Find and Replace dialog box (Ctrl + F), just type ~? into the Find what field. Excel will then return all cells that contain an actual question mark character.
In short, the tilde wildcard is your escape key for special characters. Whenever you want to search for a literal ?, *, or even another ~, add a tilde before it, so Excel doesn’t interpret it as a wildcard.
Searching in Excel is a lot faster when you’ve mastered wildcards
Once you get comfortable with wildcards, searching in Excel feels less like looking for a needle in a haystack and more like an elephant in the room. These symbols may be small, but with them, you can filter, count, and locate your data with precision, no matter how large your spreadsheet is.
Whether you’re using the asterisk for flexible searches, the question mark for exact matches, or the tilde for literal characters, these small tools can make a big difference in how quickly you find what you need.