Exceljet

Quick, clean, and to the point

Wildcard

Example of wildcard in COUNTIF function

A wildcard is a special character that let's you "fuzzy" matching in your Excel formulas. For example, this formula:

=COUNTIF(B5:B11,"*combo")

counts alls cells in the range B5:B11 that end with the text "combo". And this formula:

=COUNTIF(A1:A100,"???")

Counts all cells in A1:A100 that contain exactly 3 characters.

Available wildcards

Excel has 3 wildcards you can use in your formulas:

  • Asterisk (*) - zero or more characters
  • Question mark (?) - any one character
  • Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).

Example wildcard usage

Usage Behavior Will match
? Any one character "A", "B", "c", "z", etc.
?? Any two characters "AA", "AZ", "zz", etc.
??? Any three characters "Jet", "AAA", "ccc", etc.
* Any characters "apple", "APPLE", "A100", etc.
*th Ends in "th" "bath", "fourth", etc.
c* Starts with "c" "Cat", "CAB", "cindy", "candy", etc.
?* At least one character "a", "b", "ab", "ABCD", etc.
???-?? 5 characters with hypen "ABC-99","100-ZT", etc.
*~? Ends in question mark "Hello?", "Anybody home?", etc.
*xyz* Contains "xyz" "code is XYZ", "100-XYZ", "XyZ90", etc.

Compatible functions

Not all functions allow wildcards. Here is a list of the most common functions that do: