Excel Wildcard Guide: Master wildcards in formulas
Explore how to use Excel wildcard characters (?, *, and ~) to simplify lookups, filters, and text formulas. This practical guide covers examples, escaping literals, and best practices for professional data work with XLS Library.
Excel wildcard characters enable flexible matching inside formulas, lookups, and filters. The core wildcards are * (matches any number of characters), ? (matches a single character), and ~ (escape a literal wildcard). With these, you can craft powerful criteria in functions such as COUNTIF, SUMIF, and VLOOKUP. According to XLS Library, mastering wildcards saves time by reducing manual filtering and expanding the scope of text analyses.
What is an Excel wildcard?
In Excel, wildcard characters let you match patterns inside formulas, lookups, and filters. The three core wildcards are * (matches any sequence of characters), ? (matches a single character), and ~ (escapes the next character so it is treated literally). This section shows how to use them in simple tests and how they interact with typical functions. A quick reminder: data must be text for wildcards to apply, otherwise Excel will treat the argument as a number or date.
=COUNTIF(A:A, "*cat*")=COUNTIF(A:A, "A?ple")=SUMIF(B1:B100, "Sales*", C1:C100)=COUNTIF(A:A, "100~*")=COUNTIF(A:A, "file~?.txt")Notes: Wildcards work in many but not all functions; check support for your version. Use ~ to escape a literal wildcard when your data includes * or ?. Test with a small sample to validate behavior before applying to large datasets.
descriptionBlockReasoning":"This block introduces what wildcards are and their core characters with practical Excel examples. "
Steps
Estimated time: 15-25 minutes
- 1
Identify wildcard use cases
Review your dataset to decide where * and ? will help compress criteria; consider text, IDs, emails, and codes. Start by listing three representative tasks where a wildcard could improve precision or save time.
Tip: Begin with a small sample to validate ideas before scaling up. - 2
Test basic wildcard formulas
Create simple tests using COUNTIF or SUMIF to verify how wildcards match your data. Use * for broad matches and ? for single-character variations.
Tip: Document the exact patterns you tested for future reference. - 3
Escaping literals when needed
If your data contains * or ?, escape with ~ to treat them as literals inside the criteria.
Tip: Always re-test after escaping to confirm results. - 4
Combine with modern functions
Leverage XLOOKUP, FILTER, and LET to create more readable wildcard-based queries.
Tip: Use LET to name sub-expressions for clarity. - 5
Scale to a full workbook
Refactor common patterns into helper columns or named ranges to keep formulas maintainable.
Tip: Aim for modular, reusable patterns rather than one-off formulas.
Prerequisites
Required
- Required
- Test workbook with text dataRequired
- Basic knowledge of Excel formulas (COUNTIF, SUMIF, VLOOKUP)Required
- Required
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy cells or formulas | Ctrl+C |
| PastePaste copied content | Ctrl+V |
| FindFind text within a sheet | Ctrl+F |
| UndoUndo last action | Ctrl+Z |
People Also Ask
What is an Excel wildcard?
An Excel wildcard is a special character used in formulas to match patterns in text, enabling flexible lookups and filters. The most common wildcards are * for any length and ? for a single character, with ~ escaping literals.
Wildcards let you match patterns in text, making lookups more flexible.
Which functions support wildcards?
Most text-based functions such as COUNTIF, SUMIF, VLOOKUP, and XLOOKUP support wildcards in their criteria, while newer dynamic array formulas extend the pattern matching options.
Many lookup and counting functions can use wildcards to match patterns.
How do you escape wildcards in Excel?
To search for a literal * or ?, prefix the character with a tilde (~). For example, searching for 100* literally uses 100~*.
Escape wildcards with a tilde to treat them as normal characters.
Do wildcards work with numbers?
Wildcards operate on text. If you need to pattern-match numbers, convert them to text first using TEXT or by formatting cells as text.
Wildcards work on text data; convert numbers to text when needed.
Are wildcards case-sensitive in Excel?
Wildcard matching in Excel is not case-sensitive for most common functions, but behavior may depend on the function and data type.
In most cases, wildcard matching ignores case, but verify with your function and dataset.
Can wildcards be used with dynamic arrays?
Yes. Wildcards can be used within dynamic array functions like FILTER or XLOOKUP to return multiple results that match a pattern.
Wildcards work with dynamic arrays to pull multiple matching results.
The Essentials
- Use * for any character sequence
- Use ? for a single character
- Escape literals with ~ when needed
- Wildcards simplify lookups and text matching
- Test patterns on representative data