Wildcard in Excel
Wildcard in Excel – Full Guide with Examples for Beginners to Experts
Learn how to use wildcard in Excel like a pro. Explore symbols like *, ?, and ~ with real-world use cases in COUNTIF, VLOOKUP, SEARCH, and more.
Excel is an incredibly powerful tool, but its true magic lies in its flexibility. One of the underrated features that every Excel user—from beginner to advanced—should master is the wildcard. Whether you’re performing a VLOOKUP, counting values, or filtering text, wildcards help you deal with uncertainty in your data.
In this article, we’ll explore everything you need to know about wildcard in Excel, including what they are, how to use them, and when not to.
Table of Contents
🧠 What Is a Wildcard in Excel?
A wildcard in Excel is a special character that substitutes unknown characters in text values. It allows you to search, match, and analyze data with partial strings, saving you time and effort when exact matches aren’t possible or practical.
Common Wildcard Characters in Excel:
| Wildcard | Meaning | Example Match |
|---|---|---|
* | Any number of characters (including none) | Br* → “Brain”, “Brainiqa”, “Bruno” |
? | Exactly one character | B?g → “Bag”, “Bug” |
~ | Escapes * or ? to treat them literally | file~? → “file?” |
These wildcards can be used in formulas like COUNTIF, VLOOKUP, SEARCH, and even in Excel filters.
🎯 Why Use Wildcards in Excel?
Here are a few reasons why wildcards are a game changer:
- Handle incomplete or inconsistent data
- Simplify search and filter operations
- Perform partial matches in large datasets
- Quickly identify similar entries
🛠️ How to Use Wildcard in Excel – With Functions
Let’s dive into the most popular functions where wildcards work beautifully.
✅ 1. Using Wildcard in COUNTIF / COUNTIFS
You can count cells with partial matches.
=COUNTIF(A2:A20, “Brain*”)
🔹 This counts all entries starting with “Brain” like “Brainiqa”, “Brainstorm”, etc.
Advanced Tip (COUNTIFS):
=COUNTIFS(A2:A20, “data“, B2:B20, “?23”)
Finds rows where Column A contains “data” and Column B ends in 23 with one preceding character.
🔍 2. Wildcards in VLOOKUP
Use wildcards in VLOOKUP when you want to search based on partial text.
=VLOOKUP(“Br*”, A2:B20, 2, FALSE)
🔹 This searches for a value starting with “Br” in the first column and returns a value from the second column.
Note: Wildcards work only with exact match (FALSE).
🔍 3. SEARCH with Wildcards
SEARCH is case-insensitive and accepts wildcards.
=SEARCH(“idea“, A1)
🔹 Checks if the word “idea” appears anywhere in A1.
Use this with IF to return meaningful results:
=IF(ISNUMBER(SEARCH(“excel“, A2)), “Excel Found”, “Not Found”)
🧪 4. FILTER Function with Wildcards (Excel 365+)
If you’re using Excel 365, combine wildcards with FILTER for dynamic results.
=FILTER(A2:A20, ISNUMBER(SEARCH(“quiz“, A2:A20)))
🔹 This returns only the rows that contain “quiz” in the text.
🔐 5. Escaping Wildcards with ~
What if your actual data contains * or ?? Use the tilde (~) to escape them.
=COUNTIF(A2:A20, “Brainiqa~*2024”)
🔹 This searches for the literal “Brainiqa*2024”, not a wildcard match.
🚫 Where Wildcards Don’t Work
- Data Validation dropdowns
- Custom number formats
- Array constants
- Excel tables (structured references)
Always test your formula if unsure.
🧠 Wildcard vs Regex in Excel
Wildcards are simple and built-in, but if you need advanced matching like multiple patterns or character classes, you’ll need Power Query or Excel VBA with Regex.
🧾 Summary Table
| Function | Supports Wildcards | Example Usage |
|---|---|---|
| COUNTIF | ✅ Yes | =COUNTIF(A1:A10, "Jo*") |
| VLOOKUP | ✅ Yes | =VLOOKUP("Brain*", A2:B10, 2, FALSE) |
| SEARCH | ✅ Yes | =SEARCH("*excel*", A1) |
| FILTER | ✅ (Excel 365 only) | =FILTER(A1:A20, ISNUMBER(SEARCH("*data*", A1:A20))) |
| FIND | ❌ No | Doesn’t support wildcards |
🔚 Final Thoughts: Mastering Wildcard in Excel
Mastering the use of wildcard in Excel can drastically improve your efficiency, especially when dealing with inconsistent or massive datasets. Whether you’re a student, professional, analyst, or business owner, using wildcards makes your Excel experience smarter, faster, and more flexible.
So the next time you’re faced with uncertain text or incomplete data, let the wildcard be your secret weapon.
📈 Related Reads from Brainiqa
✅ 3 Powerful Excel Functions That Make Your Work Easierhttps://brainiqa.com/3-powerful-excel-functions-that-make-your-work-easier/
👉 Want a Free Excel Resource Kit?
Join our community at Brainiqa.com and get access to cheat sheets, templates, and bonus Excel tricks weekly!
