Wildcards in Excel: How to Use *, ? and ~ (With Examples)

The three Excel wildcards are * (any number of characters), ? (exactly one character), and ~ (escapes the other two). Use them inside COUNTIF, SUMIF, VLOOKUP, XLOOKUP, and Find & Replace to match partial text without writing one formula per variation.

Wildcards in Excel: How to Use *, ? and ~ (With Examples)

Wildcard Characters in Excel: *, ?, and ~

Wildcards only work against text — a pattern like *east matches "Northeast" and "Southeast", but it will never match the number 12345. Keep the three characters straight:

The tilde is the one people forget: if your data genuinely contains an asterisk or question mark (SKU codes often do), search for ~* or ~?. To search for a literal tilde, type ~~.

The three Excel wildcard characters

How to Use Wildcards in COUNTIF, SUMIF, and AVERAGEIF

Any function with a text criteria argument accepts wildcards. The pattern goes inside the criteria string in quotes:

1. Count by partial text: =COUNTIF(A2:A7,"*apple*") returns 4 when the list contains Apple juice, Pineapple, Apple pie, and Pineapple juice — the asterisk catches "apple" anywhere in the cell.
2. Count by prefix: =COUNTIF(A2:A7,"apple*") counts only cells that start with "apple", so Pineapple drops out.
3. Count fixed-length codes: =COUNTIF(A2:A100,"???") counts cells containing exactly three characters.
4. Sum matching rows: =SUMIF(A2:A20,"Laptop*",B2:B20) adds the revenue of every product whose name starts with "Laptop".
5. Average with a suffix: =AVERAGEIF(A2:A20,"*east",B2:B20) averages values for entries ending in "east".
6. Reference a cell plus a wildcard: =COUNTIF(A2:A20,"*"&D1&"*") builds the pattern from whatever the user types into D1 — handy for a mini search box.

If a COUNTIF with a wildcard returns 0 when matches clearly exist, the usual causes are trailing spaces or a number stored as text — see the Excel COUNTIF not working fixes. For counting cells that simply have anything in them, wildcards are the wrong tool; use COUNTIF not blank instead.

COUNTIF formula with wildcard pattern

Using Wildcards in VLOOKUP and XLOOKUP

Lookup functions match wildcards when the lookup value is a text pattern and (for VLOOKUP) range_lookup is set to FALSE:

• VLOOKUP with a prefix: =VLOOKUP("Jon*",A2:B5,2,FALSE) finds the first employee whose name starts with "Jon" and returns the department — Jon and Jonathan both match.
• VLOOKUP with a question mark: =VLOOKUP("J?n",A2:B5,2,FALSE) matches exactly three characters, so "Jon" and "Jan" match but "Joan" does not.
• XLOOKUP: use =XLOOKUP("*son",A2:A20,B2:B20,"Not found",2) — the fifth argument, match_mode 2, is what switches wildcard matching on. Without it, XLOOKUP treats the asterisk as plain text.
• Caution: wildcard lookups return the first match only. If two names both start with "Jon", sort your data or use a more specific pattern.

VLOOKUP formula using wildcard

Wildcards in Find and Replace and Filters

Wildcards are on by default in Find and Replace — no setting to enable. To clean a messy list in one pass:

1. Press Ctrl+F (or Home > Find & Select > Find), or Ctrl+H to go straight to Replace.
2. Type a pattern in Find what, for example jo* to catch Jon, John, and Jonathan in one search.
3. Click Options >> and tick Match entire cell contents if you want whole-cell matches instead of "contains".
4. Click Find All to preview every hit before touching anything — safer than blind-replacing.
5. In Replace with, enter the corrected text and click Replace All.

AutoFilter behaves the same way: in a Text Filters > Contains box, *apple* and plain apple both work, and ????? filters to five-character entries.

Find and Replace with wildcard

Wildcard Rules, Limits, and Common Errors

Four limits explain most "my wildcard formula doesn't work" moments:

• Wildcards ignore numbers. =COUNTIF(A1:A10,"12*") will not match 123 or 1234 when they are stored as numbers. Convert the column to text (or use number comparison operators instead).
• Matching is case-insensitive. *apple* matches Apple, APPLE, and pineapple alike — there is no case-sensitive wildcard switch; use EXACT-based formulas if case matters.
• Leading/trailing spaces break patterns. " apple" does not match apple*. Clean with TRIM first.
• Literal * or ? needs the tilde. Searching ~* finds a real asterisk; forgetting this makes "search for an asterisk" match everything.

Wildcard limits with numbers

The Best Free Alternative: WPS Office

Every wildcard formula above works exactly the same in WPS Spreadsheet — the free, lightweight alternative to Microsoft Excel. You get the full COUNTIF/SUMIF/VLOOKUP/XLOOKUP toolset, Find & Replace with wildcards, and WPS AI that can write or explain a pattern-based formula for you, so a forgotten tilde doesn't cost an afternoon.

WPS Spreadsheet formula example

FAQs

1. What are the wildcard characters in Excel?
Three: * matches any number of characters, ? matches exactly one, and ~ escapes the other two so you can search for a literal asterisk or question mark.

2. Why doesn't my wildcard formula work on numbers?
Wildcards only apply to text. Numbers stored as numeric values are invisible to * and ? patterns — format the cells as text or use comparison operators like ">100" instead.

3. How do I search for an actual asterisk in Excel?
Prefix it with a tilde: search for ~*. The same rule applies to question marks (~?) and to a literal tilde (~~).

4. Which Excel functions support wildcards?
All functions with a text criteria argument: COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, SEARCH, MATCH, HLOOKUP, VLOOKUP (with exact match), and XLOOKUP (with match_mode 2), plus Find and Replace and AutoFilter.

5. Are Excel wildcards case-sensitive?
No. *apple* matches "Apple", "APPLE", and "Pineapple" equally. If case sensitivity matters, use formulas built on EXACT() or FIND() instead of wildcard matching.