Wildcards in Excel: Explained
In this article, you will learn how to use Wildcards in Excel
What are Wildcards in Excel?
In Excel, wildcards are special characters that are combined with other characters in search criteria to perform partial matches or pattern-based searches in cells containing text. Wildcards are particularly useful when you want to search for specific patterns or variations of a particular word or phrase.
There are two main types of wildcards in Excel:
Asterisk (*) Wildcard:
The asterisk wildcard represents any number of characters (including zero characters) in a search term. It can be used to match any sequence of characters in a cell.
Example: Searching for "appl*" would match "apple", "application", "appliance", and so on.
Question Mark (?) Wildcard:
The question mark wildcard represents a single character in a search term. It can be used to match any single character in a cell.
Example: Searching for "te?t" would match "test", "text" and so on.
What are some uses of Wildcards in Excel?
Wildcards in Excel offer powerful functionality for performing flexible and dynamic searches, filtering, and data manipulation. Here are some common uses of wildcards in Excel:
- Filtering and Searching: Use wildcards to filter and search for specific patterns within text values or strings. For example, you can use wildcards to filter and retrieve data that starts with a specific letter or contains a particular sequence of characters.
- Data Validation:Employ wildcards to set up data validation rules for cells or ranges. For instance, you can use a wildcard pattern to validate inputs that follow a specific format or structure.
- Formulas and Functions: Utilize wildcards within formulas and functions to perform dynamic calculations and manipulations. Wildcards can be used in functions like COUNTIF, SUMIF, and VLOOKUP to match specific patterns or conditions.
- Text Manipulation: Apply wildcards when manipulating text data to perform tasks such as extracting substrings, replacing characters, or formatting. Wildcards can be useful in functions like LEFT, RIGHT, MID, and SUBSTITUTE.
These are just a few examples of the diverse applications of wildcards in Excel. Wildcards provide flexibility and efficiency in handling and manipulating data, enabling users to perform complex tasks with ease.
Filtering Account Codes and summarizing Account Balances:
Suppose you have a list of account codes and their corresponding account balances in columns A & B in Excel. Every region account codes start with a specific region code followed by the account number. Your objective is to summarize the balances by specific account types or regions. You can use wildcards with SUMIF to do the same by using the formula shown below the sum function adds up all the account balances where the Account codes start with AP. Please note because we use the “*” wildcard using just one “*” allows us to match all account codes having starting with AP irrespective of the number of digits following AP.
Similarly we can also use the “?” wildcard for filtering the account codes as well. As seen in the formula below, when using the “?” wildcard we need to mention it for as many characters expected in the search string.
If the “?” is used a single time like below, the search doesnot yield any results as all EMEA account codes have EM followed by four characters and our formula is only searching for EM followed by a single character.
