You need to find or filter data in Excel when you do not know the exact text. This is common with customer names, product codes, or partial descriptions. Excel’s wildcard characters let you perform these fuzzy searches. This article explains how to use the asterisk and question mark wildcards in functions like VLOOKUP, COUNTIF, and the Find and Replace dialog.
Key Takeaways: Excel Wildcard Search
- Asterisk (*): Matches any number of characters, useful for finding text that starts or ends with known letters.
- Question Mark (?): Matches exactly one character, ideal for finding text with a single unknown letter.
- Find and Replace (Ctrl+F): Use wildcards here to search or replace text patterns across your entire worksheet.
What Excel Wildcards Can Do
Wildcards are special characters that stand in for other characters in a text search. They turn exact-match searches into pattern-based searches. Excel supports three main wildcards: the asterisk (*), the question mark (?), and the tilde (~). The asterisk represents any sequence of characters, including zero characters. The question mark represents any single character. The tilde is an escape character used when you need to find a literal asterisk or question mark.
You can use these wildcards in many Excel features. They work in functions that have a criteria argument, such as COUNTIF, SUMIF, and AVERAGEIF. They are also essential for lookup functions like VLOOKUP and XLOOKUP when used with the TRUE argument for an approximate match. Furthermore, the Filter feature and the standard Find and Replace dialog fully support wildcards for searching cell contents.
When to Use Each Wildcard
Use the asterisk when the length of the unknown text varies. For example, searching for “North*” will find “North”, “Northeast”, and “Northern Region”. Use the question mark when the position and number of unknown characters are fixed. Searching for “Sm?th” will find “Smith” and “Smyth” but not “Smooth”. You can combine them in a single pattern, like “A*?Z”, which finds text starting with “A”, ending with “Z”, and with at least one character in between.
Steps to Use Wildcards in Excel Functions
The most common use is within worksheet functions. You place the wildcard pattern inside quotation marks as part of the criteria argument.
- Use COUNTIF with a wildcard
To count cells in range A1:A10 that contain the word “report” anywhere, type the formula=COUNTIF(A1:A10, "*report*"). The asterisks before and after mean any text can come before or after the word. - Use SUMIF with a partial match
To sum values in B1:B10 where the corresponding cell in A1:A10 starts with “INV-“, use=SUMIF(A1:A10, "INV-*", B1:B10). This sums all values for invoice numbers. - Use VLOOKUP for a fuzzy match
Set the last argument of VLOOKUP to TRUE. Use a wildcard in the lookup_value by concatenating it with an asterisk, like=VLOOKUP("Max*", A1:B20, 2, FALSE). This finds the first value in column A starting with “Max”. Note: VLOOKUP with FALSE requires the exact wildcard pattern to be in the lookup table.
Using Wildcards in Find and Replace and Filters
You can also apply wildcards directly to Excel’s interface tools for quick data manipulation.
- Search with Ctrl+F
Press Ctrl+F to open the Find dialog. Type your wildcard pattern, such as “?ean” to find “Jean”, “Dean”, or “lean”. Check the box for “Match entire cell contents” if you want the pattern to match the whole cell. - Replace text patterns
In the Find and Replace dialog, go to the Replace tab. To replace all five-letter words ending in “ght” with “Light”, enter “???ght” in Find what and “Light” in Replace with. Click Replace All. - Filter data with wildcards
Click the filter arrow on a column header. In the search box of the filter menu, type a pattern like “South*” and press Enter. The sheet will now only show rows where that cell begins with “South”.
Common Mistakes and Limitations
Wildcards Do Not Work with Exact Match Lookups
The XLOOKUP or VLOOKUP function with the exact match argument set to FALSE will not interpret a wildcard in the lookup_value as a pattern. It will search for the literal text “A*”, including the asterisk. For a fuzzy lookup, you must use TRUE for the match_mode or use a function like INDEX/MATCH with a wildcard inside a MATCH function.
Leading and Trailing Spaces Break Matches
If your data has extra spaces, a search for “*report*” might fail. Clean your data first with the TRIM function, or include spaces in your pattern, such as “* report *”.
Case Sensitivity
Standard wildcard searches in functions and Find and Replace are not case-sensitive. “*report*” will find “REPORT”, “Report”, and “rePort”. If you need a case-sensitive search, you must use functions like FIND or SEARCH within more complex formulas.
Excel Wildcard Characters Comparison
| Item | Asterisk (*) | Question Mark (?) | Tilde (~) |
|---|---|---|---|
| Primary Use | Match any number of characters | Match exactly one character | Escape character to find literal * or ? |
| Example Pattern | “*east” finds “Northeast” and “east” | “Gr?y” finds “Gray” and “Grey” | “~*” finds a cell containing an asterisk |
| Works in Find and Replace | Yes | Yes | Yes |
| Works in COUNTIF/SUMIF | Yes | Yes | Yes, to search for a literal tilde |
| Character Count | Zero or more | Exactly one | None, it modifies the next character |
You can now use asterisks and question marks to search for partial text in Excel. Apply them in your next COUNTIF formula to summarize data based on text patterns. For more advanced pattern matching, explore using the tilde to search for special characters. Remember that combining wildcards, like “A*?Z”, can create powerful and specific search criteria for your datasets.