Excel Text Wildcards: Asterisk, Question Mark, and Tilde

How-to basics · Updated November 18, 2026

Excel text wildcards are special characters you can use in criteria to match text without typing the whole value: an asterisk (*) matches any number of characters, a question mark (?) matches one character, and a tilde (~) treats the next wildcard character as literal text. They are useful in functions such as COUNTIF and SUMIF, and in certain lookup and Find operations.

What Excel wildcards mean

A wildcard stands in for text whose exact characters you do not need to specify. Excel supports three wildcard characters in compatible criteria:

  • Asterisk (*): Matches zero or more characters. The criterion North* can match North, Northern, or Northwind.
  • Question mark (?): Matches exactly one character. The criterion Jo?n can match Joan or John, but not Jon or Joanne.
  • Tilde (~): Escapes the next wildcard character so Excel looks for it literally. Use ~* to find an actual asterisk, ~? for an actual question mark, and ~~ for an actual tilde.

Wildcards are not general-purpose symbols that work in every Excel formula. They are interpreted by functions and tools that specifically support wildcard matching. In a formula such as a direct comparison, typing an asterisk does not automatically make it a wildcard.

How wildcard criteria are read

Excel applies a wildcard pattern to the text being checked. Putting an asterisk at the end of a word looks for text that starts with that word; putting one at the beginning looks for text that ends with it. Asterisks on both sides look for the pattern anywhere in the text. For example, *west* can match Westfield, Northwest, or a longer value containing “west.”

Question marks are useful when the number of unknown characters is fixed. The pattern ???-#### describes three characters, a hyphen, then four characters. It does not validate whether those characters are digits; the question marks stand for any single character. Wildcard matching is generally not case-sensitive in the criteria functions discussed here, so north* can match “North Ridge.”

Criteria can also combine ordinary text with wildcard symbols. When a criterion refers to a cell, join the wildcard characters and the cell reference with the ampersand (&). Excel then evaluates the resulting pattern as the criterion. This is handy when users need to change a search word without editing the formula.

Use wildcards with COUNTIF and COUNTIFS

COUNTIF counts cells that satisfy one criterion. With product names in A2:A100, this formula counts names beginning with “Pro”:

=COUNTIF(A2:A100,"Pro*")

If the range contains “Pro Keyboard,” “Professional Plan,” “Projector,” and “Keyboard,” the result is 3. The asterisk allows any characters after “Pro,” including a space or no further characters. To count entries containing a word anywhere, put a wildcard on each side.

With product names in A2:A100 and a search word in D2, this formula counts names containing the text in D2:

=COUNTIF(A2:A100,"*"&D2&"*")

If D2 contains “key,” the formula counts values such as “Keyboard” and “Wireless key set.” A blank D2 creates the criterion **, which can match text entries broadly; if an empty search should return zero instead, add an IF check around the count.

COUNTIFS accepts multiple criteria, so a wildcard can be one condition among several. With product names in A2:A100 and regions in B2:B100, this counts names starting with “Pro” in the West region:

=COUNTIFS(A2:A100,"Pro*",B2:B100,"West")

Each criteria range must have the same dimensions. Wildcard criteria are useful for flexible text categories, but they do not replace careful data cleanup: leading spaces or inconsistent naming can change which cells match.

Use wildcards with SUMIF and related criteria functions

SUMIF can add numbers associated with text that matches a wildcard pattern. With product names in A2:A100 and sales amounts in B2:B100, this sums sales for names beginning with “Pro”:

=SUMIF(A2:A100,"Pro*",B2:B100)

If matching rows have sales of 20, 35, and 15, the result is 70. SUMIFS can apply several criteria, while AVERAGEIF and AVERAGEIFS can calculate an average for matching rows. The wildcard belongs in the criterion argument; the sum or average range still needs to contain the values to calculate.

A common mistake is to place a wildcard in the sum range or assume that it changes the calculation itself. It only controls which records qualify. Also, wildcards are intended for text matching: do not rely on a pattern such as *5 to select numeric values ending in 5. If numbers need to be tested, use numeric criteria or convert and inspect the data deliberately.

Wildcard lookups: MATCH, VLOOKUP, and XLOOKUP

MATCH supports wildcards when its third argument is 0, which requests an exact match (including a wildcard pattern). With names in A2:A20 and a lookup pattern in D2, this returns the position of the first matching name:

=MATCH("Sam*",A2:A20,0)

If “Sample” is the first matching value in A2:A20, the result is 1; MATCH returns a position within the range, not the worksheet row number. Use 0 here: other MATCH modes have different behavior and are not the wildcard exact-match mode.

VLOOKUP also permits wildcards in its lookup value when its range lookup argument is FALSE. With employee names in A2:B20, where column B contains departments, this returns the department for the first name beginning with “Sam”:

=VLOOKUP("Sam*",A2:B20,2,FALSE)

In Microsoft 365, XLOOKUP has a wildcard match mode. With names in A2:A20 and departments in B2:B20, this returns the department for the first name beginning with “Sam”:

=XLOOKUP("Sam*",A2:A20,B2:B20,"Not found",2)

The final argument, 2, requests wildcard matching. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not have it. In all these lookup examples, a pattern may match more than one item, but the function returns the first match it encounters. Sort order and duplicate names therefore matter if the result must identify a unique record.

Escape wildcard characters when they are real text

If a cell contains a literal asterisk as part of a product code, a criterion containing an unescaped asterisk will treat it as a wildcard rather than search for that symbol. Put a tilde immediately before the character to escape it. With codes in A2:A100, this counts cells containing the literal text “Item*”:

=COUNTIF(A2:A100,"Item~*")

The same rule applies to a literal question mark: use ~?. To match a literal tilde, double it as ~~. Escaping matters most when punctuation is part of an identifier, filename, or imported label, and the goal is to find that exact punctuation rather than use it as a pattern.

If a search term comes from a cell and may itself contain asterisk, question mark, or tilde characters, simply joining it to wildcards can produce an unintended pattern. For reliable literal searching, escape the characters in the supplied text before constructing the criterion. The required steps depend on how the text is assembled, so test the resulting criterion against sample values before using it for important counts or lookups.

Find and Replace, and what wildcards do not do

Excel’s Find and Replace tools can also use wildcards. On Windows, press Ctrl+H to open Replace, then select Options and turn on Use wildcards. On Mac, open Edit > Find > Replace and expand the search options to access wildcard matching; labels and placement can vary by Excel release. Wildcards are not enabled in every Find operation by default, so check the option rather than assuming an asterisk will be interpreted as a pattern.

Wildcards are different from regular expressions. They offer only the basic “any sequence,” “one character,” and escape behaviors described above; they do not provide regex features such as character classes or repetition counts. They also do not automatically trim spaces, normalize accents, or verify that a pattern represents a valid phone number or code.

Functions such as FILTER do not use the wildcard syntax as a general search pattern in their include argument. For a more complex search in Microsoft 365, a formula can combine text-search functions with FILTER, but that is a different technique, not wildcard matching. Use a wildcard-enabled criterion function when the task is a straightforward count, sum, average, or lookup; choose a different approach when the logic needs more control.

Common problems and quick checks

  • No results appear: Confirm that the function supports wildcards, the pattern is in the criteria argument, and text is actually stored in the checked cells.
  • Too many results appear: A leading and trailing asterisk searches anywhere in a value. Remove one or both if the text must occur at a particular edge.
  • A question mark matches unexpected text: It stands for any single character, not specifically a letter or digit.
  • A symbol in the data is treated as a pattern: Escape literal *, ?, or ~ with a leading tilde as appropriate.
  • A lookup returns the wrong record: Several entries may match. Check the first matching row and use a more specific pattern or a unique key.
  • A result changes after copying a formula: Check whether the ranges need absolute references, such as $A$2:$A$100, before filling the formula down.

For diagnosis, try the same criterion on a small range with known examples. Include one value that should match and one that should not, then confirm whether spaces, punctuation, and cell types are as expected. This separates a pattern problem from a range-reference or data-quality problem.

Frequently Asked Questions

What is the Excel wildcard for any number of characters?
The asterisk (*) matches zero or more characters. For example, East* matches “East,” “East Side,” and “Eastern.”

What wildcard matches exactly one character in Excel?
The question mark (?) matches exactly one character. The pattern A?C can match “ABC” or “A7C,” but not “AC” or “ABBC.”

How do I search for an actual asterisk in Excel?
Escape it with a tilde: ~*. For example, a COUNTIF criterion of "Item~*" looks for the literal asterisk after “Item,” rather than treating it as a wildcard.

Are Excel wildcards case-sensitive?
No. COUNTIF and the common wildcard lookup examples are not case-sensitive. If capitalization must distinguish matches, use a case-sensitive approach rather than relying on wildcard criteria.

Can I use wildcards with XLOOKUP?
Yes, in Microsoft 365 and Excel 2021 or later, set XLOOKUP’s match_mode argument to 2. The lookup value can then contain * or ?, and ~ escapes a wildcard character.

Why does my wildcard formula not match a number?
Wildcards are for text patterns, and numeric cell values are not necessarily treated as text just because a pattern is used. Use numeric criteria for numeric comparisons, or deliberately convert values to text when text-pattern matching is the intended task.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.