Excel Wildcard Characters: The Complete Reference (* ? ~)
Excel has exactly three wildcard characters: the asterisk (*), the question mark (?), and the tilde (~). They let you match partial text—“every value starting with North”, “any 5-character product code”, “cells that literally contain an asterisk”—but they only work in certain functions and features. Knowing the boundary between where wildcards work and where they silently fail is the difference between a correct report and one that quietly counts zero rows. This reference covers all three characters, every context that supports them, and the workarounds for the contexts that don’t.
The Three Wildcards
| Character | Matches | Example pattern | Matches | Doesn’t match |
|---|---|---|---|---|
* (asterisk) | Any number of characters, including zero | North* | North, Northeast, Northern Region | South North |
? (question mark) | Exactly one character | SKU-??? | SKU-001, SKU-A4B | SKU-12, SKU-1234 |
~ (tilde) | Nothing itself—escapes the next *, ?, or ~ so it’s treated literally | 2~* | 2* | 24, 2x |
Two details trip people up constantly:
*matches zero characters too. The pattern*berry*matches the bare word “berry”, not just “blueberry” or “berry pie”. AndNorth*matches “North” itself.- Wildcard matching is case-insensitive in formulas—
apple*matches “Apple Inc.” (Find and Replace is also case-insensitive unless you check Match case.) For case-sensitive partial matching you need a formula built on FIND, which is case-sensitive but does not accept wildcards at all.
A pattern with no asterisks anchors at both ends. ?at matches “cat” and “hat” but not “chat”—the string must be exactly three characters. To express “contains”, wrap the term in asterisks: *at*.
Where Wildcards Work
COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
The conditional aggregation family is where wildcards earn their keep. The criteria argument accepts a wildcard pattern as text:
=COUNTIF(A2:A500, "*widget*") // cells containing "widget"
=COUNTIF(A2:A500, "??-????") // 2 chars, dash, 4 chars
=SUMIF(A2:A500, "North*", B2:B500) // sum B where A starts with "North"
=SUMIFS(C2:C500, A2:A500, "*Ltd", B2:B500, ">100")Note: # has no wildcard meaning in Excel, and wildcards can’t distinguish letters from digits—??-???? matches any characters in those positions.
To make the pattern dynamic, concatenate the wildcard with a cell reference:
=COUNTIF(A2:A500, "*"&E1&"*")This counts every cell in A2:A500 containing whatever is typed in E1—the standard pattern for building a search box on a report. The full syntax for these functions is covered in our COUNTIF and SUMIFS guides.
MATCH, XMATCH, VLOOKUP, HLOOKUP
MATCH accepts wildcards when match_type is 0 (exact match):
=MATCH("*ACME*", A2:A100, 0)This returns the position of the first cell containing “ACME”—handy when vendor names arrive with inconsistent prefixes. XMATCH needs match_mode set to 2 to enable wildcards; its default exact mode treats * literally.
VLOOKUP and HLOOKUP accept wildcards only when the last argument is FALSE (exact match):
=VLOOKUP("INV-2024*", A2:C900, 3, FALSE)With TRUE (approximate match), the asterisk is treated as a literal character and the lookup almost certainly fails. This is one of the most common silent wildcard bugs.
XLOOKUP — but only with match_mode 2
XLOOKUP does not honor wildcards by default. Its default match_mode of 0 means exact match, literal characters included. You must explicitly pass 2 as the fifth argument:
=XLOOKUP("*Hardware*", A2:A900, B2:B900, "Not found", 2)Forget the 2 and XLOOKUP searches for a cell whose literal content is *Hardware*, returns the if_not_found value, and gives no hint about why. If you migrated formulas from VLOOKUP with FALSE—where wildcards just worked—this behavior change will bite you.
SEARCH (but not FIND)
The SEARCH function accepts wildcards in its find_text argument:
=SEARCH("inv?ice*total", A2)Its case-sensitive twin FIND does not—any * or ? you pass to FIND is matched literally. This pairing matters because SEARCH is the standard workaround for contexts that reject wildcards entirely (more below).
Find and Replace (Ctrl+F / Ctrl+H)
Wildcards work in the Find what box of Find and Replace, and they enable surgical bulk edits. The classic move—delete everything after a character across a whole column:
- Find what:
(*Replace with: (leave empty)
That strips a trailing parenthetical like ” (old)” from every cell in one pass. Similarly, find *@ and replace with nothing to keep only the domain from a column of email addresses.
Two warnings specific to this dialog. First, wildcards work in Find what only—* in the Replace with box inserts a literal asterisk; there’s no way to reference the matched text. Second, check Match entire cell contents under Options when your pattern should describe the whole cell; otherwise 2* in Find what matches any cell merely containing a 2.
Filters
AutoFilter’s search box and the Custom AutoFilter dialog (Text Filters → Custom Filter) both accept * and ?. Typing *east* in the filter search box shows only rows containing “east”. The Advanced Filter criteria range accepts them too: putting North* under a Region header filters to regions starting with “North”. The FILTER function, however, is a formula—it follows formula rules and needs the SEARCH workaround below.
Conditional formatting (Text that Contains rule only)
The built-in “Text that Contains” conditional formatting rule accepts wildcards. Formula-based rules do not, because they evaluate like any other formula—and data validation custom formulas follow the same formula rules, so no wildcards there either.
Where Wildcards Do NOT Work
This list causes more wrong answers than the previous section combined:
| Context | Behavior with * or ? |
|---|---|
= comparison: =IF(A2="North*", ...) | Treated literally; only matches a cell containing exactly North* |
| XLOOKUP / XMATCH at default match_mode | Treated literally |
| FIND, EXACT, SUBSTITUTE | Treated literally |
FILTER, SUMPRODUCT, and other array formulas using = | Treated literally |
| Formula-based conditional formatting | Treated literally |
The universal workaround is ISNUMBER(SEARCH(...)). SEARCH returns a number when it finds the text and #VALUE! when it doesn’t, so wrapping it in ISNUMBER produces a clean TRUE/FALSE you can use anywhere a logical test is expected—inside the IF function, FILTER, or a conditional formatting formula:
=IF(ISNUMBER(SEARCH("north", A2)), "Match", "No match")
=FILTER(A2:C900, ISNUMBER(SEARCH($E$1, A2:A900)), "None")Because SEARCH itself does the substring matching, you usually don’t even need the asterisks—SEARCH("north", A2) already means “contains north”.
Escaping with the Tilde
When the text you’re matching genuinely contains *, ?, or ~, prefix that character with a tilde:
| To match literally | Type |
|---|---|
* | ~* |
? | ~? |
~ | ~~ |
So counting cells that contain an actual asterisk (a common footnote marker in pasted data):
=COUNTIF(A2:A500, "*~**")Reading left to right: * (anything) + ~* (a literal asterisk) + * (anything). The same escaping works in Find and Replace—to find cells containing a question mark, search for ~?; to find a tilde, search for ~~.
One quirk worth knowing: a tilde followed by any character other than *, ?, or ~ is treated as a literal tilde in most contexts, but don’t rely on it—always double the tilde when you mean a tilde.
Quick Reference: Worked Examples
| Goal | Formula or technique |
|---|---|
| Count cells containing “tax” | =COUNTIF(A:A, "*tax*") |
| Count cells with exactly 6 characters | =COUNTIF(A:A, "??????") |
| Sum amounts where vendor ends in “LLC” | =SUMIF(A:A, "*LLC", B:B) |
| Position of first cell containing E1’s text | =MATCH("*"&E1&"*", A:A, 0) |
| Lookup by partial key | =XLOOKUP("*"&E1&"*", A:A, B:B, "", 2) |
| Contains-test inside IF | =IF(ISNUMBER(SEARCH("tax", A2)), 1, 0) |
| Strip text after a hyphen, whole column | Ctrl+H, Find -*, Replace empty |
Count cells containing a literal ? | =COUNTIF(A:A, "*~?*") |
Note that wildcards match text. A criteria like "*5*" will not match the number 45 stored as a true number in COUNTIF—numeric values aren’t run through text matching. Convert with TEXT or test differently when your data is numeric.
FAQ
Why does my wildcard work in COUNTIF but not in my IF formula?
The = operator never interprets wildcards—only specific functions do (COUNTIF/SUMIF families, MATCH, VLOOKUP/HLOOKUP exact mode, SEARCH, and XLOOKUP with match_mode 2). In IF, replace the comparison with ISNUMBER(SEARCH("text", A2)) to get a contains-style test.
How do I search for an actual asterisk in Find and Replace?
Type ~* in the Find what box. The tilde tells Excel to treat the next character literally. Use ~? for a literal question mark and ~~ for a literal tilde.
Do wildcards work with numbers?
Only when the value is text. A cell containing the true number 1045 won’t match the COUNTIF criteria "*04*". Either convert the values with TEXT(A2,"0") in a helper column, or use a SUMPRODUCT/SEARCH construction that coerces the numbers to text first.
Is there a wildcard for “one or more characters”?
No. Excel’s * means zero or more, and there’s no regex-style +. To require at least one character before “berry”, use the pattern ?*berry—the ? consumes one mandatory character and the * takes the rest. (Excel 365 now offers REGEXTEST for true regular expressions when wildcards aren’t expressive enough.)
Why does XLOOKUP return #N/A when VLOOKUP with the same wildcard worked?
VLOOKUP enables wildcards automatically in exact-match mode (FALSE), but XLOOKUP requires you to opt in by setting its fifth argument, match_mode, to 2. Without it, XLOOKUP looks for the literal asterisk in your lookup value.
