Excel Wildcard Characters: A Guide with Examples

Excel wildcard characters are special symbols that help users perform partial matches and flexible data operations across large datasets. While Excel doesn't support full regex functionality, you can use wildcards with creative formula structuring (functions like SEARCH(), TEXT(), and ISNUMBER()).

Building competency with wildcard characters becomes easier when you have solid Excel fundamentals in place. Our Introduction to Excel course walks you through essential skills including interface navigation, cell references, and basic formulas—creating the foundation you need to implement the wildcard techniques covered in this guide effectively.

What Are Excel Wildcard Characters?

Wildcards are special symbols that replace characters in a text string, allowing you to search for patterns rather than exact matches. Excel recognizes three wildcard characters, each serving a distinct purpose in pattern matching and text operations.

The asterisk (*)

The asterisk (*) matches any number of characters, including none. For example, App* would match "Apple", "Application", "App", and "Approach".

This wildcard is useful when you know the beginning of a text string but want to capture all variations that follow.

The question mark (?)

The question mark (?) matches exactly one character. Using B?t would match "Bat," "Bet," "Bit," and "But," but not "Boat" or "Boot."

This precision makes it ideal for situations where you know the length and most characters of your target text.

The tilde (~)

The tilde (~) serves as an escape character, allowing you to search for actual asterisks or question marks in your data. When you need to find text that literally contains * or ? characters, placing a tilde before them tells Excel to treat them as regular characters rather than wildcards.

For example, ~*Sale would match the literal “*Sale” rather than treating the asterix as a wildcard.

Using Wildcards in Excel Formulas

Several Excel functions support wildcard characters, making them valuable for conditional calculations and lookup operations. The most commonly used functions that accept wildcards include COUNTIF(), COUNTIFS(), SUMIF(), SUMIFS(), AVERAGEIF(), AVERAGEIFS(), SEARCH(), VLOOKUP(), HLOOKUP(), XLOOKUP(), MATCH(), and XMATCH().

Each of these functions can leverage wildcards to perform flexible pattern matching, allowing you to work with partial data matches and handle variations in your datasets effectively.

COUNTIF() with wildcards

The COUNTIF() function becomes particularly useful when combined with wildcards for counting cells that match specific patterns. Here's a basic example:

=COUNTIF(A2:A12, "*AA*")

This formula counts all cells in the range A2:A12 that contain "AA" anywhere within the text. The asterisks before and after "AA" tell Excel to match any characters before or after this sequence.

For dynamic criteria, you can reference a cell containing your wildcard pattern:

=COUNTIF(A2:A12, E1)

In this case, cell E1 might contain "*AA*" or any other wildcard pattern. This approach allows you to change your search criteria without modifying the formula itself, making your spreadsheet more flexible and user-friendly.

VLOOKUP() and XLOOKUP() with wildcards

Lookup functions can use wildcards for partial matches, which is helpful when your lookup values aren't exact matches. Here are examples of both approaches:

=VLOOKUP("John*", A:C, 2, FALSE)

This VLOOKUP() formula searches for any entry in column A that starts with "John" and returns the corresponding value from column 2. The FALSE parameter ensures the function uses exact match mode, which is required for wildcard functionality.

For XLOOKUP(), the syntax is slightly different:

=XLOOKUP("*Adams", A:A, C:C, "Not found", 2)

The match mode parameter (2) tells XLOOKUP() to use wildcard matching. This formula finds any entry ending with "Adams" and returns the corresponding value from column C. It will return “Not found” if there is no match. 

IF() with wildcard workaround

While the IF() function doesn't directly support wildcards, you can achieve similar functionality by combining IF() with COUNTIF():

=IF(COUNTIF(A1, "Amy*"), "Yes", "No")

This formula checks if cell A1 contains text starting with "Amy" and returns "Yes" if it does, "No" if it doesn't. The COUNTIF() function handles the wildcard matching, while IF() processes the result.

SUMIF() with wildcards

The SUMIF() function can sum values based on wildcard criteria, making it useful for aggregating data with similar patterns:

=SUMIF(A:A, "*a*", C:C)

This formula sums all values in column C where the corresponding cell in column A contains the letter "a" anywhere in the text. The wildcard pattern "a" captures any text containing "a" regardless of position.

Using Wildcards in Excel Tools

Wildcards extend beyond formulas and can be used in several Excel tools for searching, matching, and filtering data. These built-in features make wildcards accessible even when you're not writing complex formulas.

Wildcards in Find and Replace

Excel's Find and Replace dialog supports wildcards for locating variable patterns in your data. To enable wildcard functionality, you need to check the "Use wildcards" option in the Find and Replace dialog.

You can use the asterisk (*) to find patterns with varying lengths. For example, searching for "Product*X" would locate "Product-A-X," "Product-Premium-X," and "Product-X." Similarly, the question mark (?) helps find patterns with specific character positions, like "20??" to find any four-digit number starting with "20."

When you need to search for actual asterisks or question marks in your data, use the tilde (~) as an escape character. Searching for "~*Sale" would find cells containing the literal text "*Sale" rather than treating the asterisk as a wildcard.

Wildcards in filter and search features

Excel's filtering capabilities support wildcards, particularly useful in text filters with options like "contains," "begins with," and "ends with." These filters automatically interpret wildcard characters when you include them in your criteria.

For example, applying a filter with "Jo*" in the "begins with" option would display all names starting with "Jo" - including "John," "Joseph," "Joan," and "Jonathan." This approach works well for quick data exploration without writing formulas.

However, built-in search features have some limitations compared to advanced filter options. While basic wildcard patterns work effectively, complex multi-criteria filtering may require combining wildcards with custom formulas or using Excel's Advanced Filter feature for more sophisticated pattern matching.

Working with numbers and wildcards

Using wildcards with numbers requires special consideration because Excel treats wildcard operations as text-based functions. When you apply wildcard patterns to numeric data, Excel automatically converts the numbers to text, which can lead to unexpected matching behavior.

For effective number matching with wildcards, you have several workaround options. You can convert numbers to text using the TEXT() function, Text to Columns feature, or specific formulas designed for this purpose.

A practical approach involves combining SEARCH(), ISNUMBER(), and SUMPRODUCT() functions:

=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))

This formula counts how many cells in the range B2:B12 contain the digit "4" anywhere within the number. For summing values where numbers contain specific patterns, you can extend this approach:

=SUMPRODUCT(--(ISNUMBER(SEARCH("4*", TEXT(B2:B12,"0")))), C2:C12)

This formula sums values in column C where the corresponding numbers in column B contain “4” followed by any digits. 

For date matching, use the TEXT() function to convert dates into searchable text patterns:

=TEXT(C2:C12, "mmddyyyy")

This converts dates into a consistent text format that wildcards can process effectively. You can then apply wildcard patterns to find specific date components: 

=COUNTIF(TEXT(C2:C12,"mmddyyyy"),"*0525*")

The formula counts dates that contain “0525” (May 25th in any year). 

Certain scenarios involve numbers stored as text, such as ZIP codes, product IDs, or reference numbers. In these cases, wildcards can match them directly as text strings. For example, filtering codes with patterns like "123*" would match "12345," "123-AB," and "123XYZ," while "??-45" would match "AB-45," "12-45," and "XY-45."

Wildcard Limitations and Surprises

Not all Excel functions support wildcard functionality, which can create confusion when you expect certain formulas to work with pattern matching. The IF() function, for example, doesn't directly accept wildcards, requiring workarounds like combining it with COUNTIF() as demonstrated earlier.

Wildcards only work with text in formulas, meaning numeric values must be converted to text before wildcard operations can process them effectively. This text conversion requirement can lead to unexpected results if you're not aware of this limitation.

Be cautious with false positives in lookup formulas when using broad wildcard patterns. For instance, VLOOKUP("*Smith*", ...) might return unintended matches like "Smithson," "Blacksmith," or "Goldsmith" when you only wanted "Smith." This behavior requires careful pattern design to avoid capturing unwanted results.

Another common surprise occurs with leading or trailing spaces in your data. Wildcards treat spaces as characters, so "John*" won't match " John" (with a leading space). Data cleaning becomes important when working with wildcards to ensure consistent results.

Additionally, wildcard matching is case-insensitive by default. This means patterns like "apple*" will match "Apple," "APPLE," and "ApPlE." This behavior is convenient for general searches but might cause unintended matches if you require case-sensitive matching. To achieve case-sensitive matches, consider using functions like FIND() combined with ISNUMBER() instead. 

Tips When Working with Wildcards in Excel

Wildcards excel at merging fuzzy matched data, particularly useful when dealing with inconsistent spellings or data entry variations. For example, you might have "Homel" and "Gomel" in your dataset representing the same location. Using wildcard patterns like "*omel" can help identify and consolidate these similar entries.

Phone number formatting often varies across datasets, with different prefixes or country codes. You can use wildcards to standardize these inconsistencies. For instance, you can use Find and Replace to convert UK international format numbers to domestic format:

Find: 44??????????
Replace with: 0

This pattern matches any UK number starting with "44" followed by ten digits, effectively converting numbers like "447123456789" to "07123456789" for consistent domestic formatting.

When you need to exclude specific results from your wildcard matches, combine multiple SUMIF() or COUNTIF() functions. This technique works well for subtracting unwanted matches:

=SUMIF(..., "00123H*") - SUMIF(..., "00123H*-SP")

This formula sums all values matching "00123H*" but excludes those ending with "-SP," giving you more precise control over your wildcard matching.

Formatting changes work effectively alongside wildcards using functions like REPLACE(), SUBSTITUTE(), or TEXT(). These functions can clean and standardize your data before or after applying wildcard operations, ensuring consistent results across your analysis.

When working with product codes or reference numbers, wildcards help identify patterns and exceptions. You might use *-TEMP to find all temporary codes, or ??-??-???? to locate entries following a specific format structure, making data validation and cleanup more efficient.

Conclusion

Whether you're consolidating similar entries, standardizing phone number formats, or filtering large datasets for specific patterns, wildcards offer a practical alternative to exact matching. They bridge the gap between basic text searches and more complex regex functionality, making pattern matching accessible within Excel's familiar environment.

To expand your Excel capabilities beyond wildcard operations, our Excel Fundamentals skill track offers a comprehensive path covering data preparation, analysis, and visualization techniques. For those ready to apply these skills in analytical contexts, our Data Analysis in Excel course provides hands-on experience with PivotTables and advanced logical functions that complement the pattern-matching skills you've learned here.