Home › Excel basics › Wildcards (* and ?)

Wildcards in Excel: *, ? and ~ with Examples

Wildcards let you match text you only partly know: everything that starts with "North", every code with exactly five characters, every cell that contains "invoice". Excel has three of them.

Excel CSE · 2 min read
In this guide
  1. The three wildcards
  2. Common patterns
  3. In formulas
  4. Where they don't work
  5. Find & Replace and filters
  6. FAQ

The three wildcard characters

WildcardMatchesExampleMatches
*Any number of characters (also none)"North*"North, Northeast, North region
?Exactly one character"P-10?"P-101, P-102, P-10A
~Makes the next * or ? a normal character"Total~*"Total* (with a real asterisk)

Wildcards are not case-sensitive and only work on text, not on numbers.

Common patterns

  • Starts with: "Anna*"
  • Ends with: "*.pdf"
  • Contains: "*invoice*"
  • Exactly 5 characters: "?????"
  • Contains, using a cell: "*"&E2&"*"

Which formulas support wildcards

FunctionExample
COUNTIF / COUNTIFS=COUNTIF(A2:A100, "*invoice*")
SUMIF / SUMIFS=SUMIF(B2:B6, "A*", C2:C6)
AVERAGEIF(S)=AVERAGEIF(A2:A10, "North*", C2:C10)
VLOOKUP (exact match)=VLOOKUP("Man*", A2:C7, 3, FALSE)
MATCH (match type 0)=MATCH("*berg", A2:A50, 0)
XLOOKUP=XLOOKUP("Man*", B2:B6, D2:D6, , 2): match_mode 2 turns wildcards on
SEARCH=SEARCH("inv*2026", A2)

Lookups return the first match. If several values fit the pattern, make it more specific.

Where wildcards don't work

A normal comparison like =IF(A2="North*", …) looks for the literal text "North*". To test "starts with" or "contains" in IF, use:

  • Contains: =IF(ISNUMBER(SEARCH("north", A2)), "Yes", "No")
  • Starts with: =IF(LEFT(A2, 5)="North", "Yes", "No")
  • Or with COUNTIF on one cell: =IF(COUNTIF(A2, "North*"), "Yes", "No")

FILTER doesn't support wildcards either; use ISNUMBER(SEARCH()) there too. See FILTER.

In Find & Replace and filters

Wildcards also work in Ctrl+F / Ctrl+H. Replacing (*) with nothing, for example, removes a space followed by any text in brackets, such as " (copy)". In a filter dropdown, the search box and Text Filters › Contains accept them as well.

Frequently asked questions

How do I search for an actual asterisk or question mark?

Put a tilde in front of it: "~*" finds a real asterisk and "~?" a real question mark.

Do wildcards work with numbers?

No. A wildcard criterion only matches text. Numbers stored as real numbers won't match "12*".

Why doesn't my IF formula work with *?

IF compares literally. Use ISNUMBER(SEARCH()) or COUNTIF inside the IF instead.