A Discussion On Wildcard Characters in Excel

In the world of Microsoft Excel, one of the most powerful tools at your disposal is the wildcard. The wildcard is a character or symbol that represents one or more unknown characters, allowing for flexible and dynamic data manipulation. Wildcards can be used in searching, filtering, or performing calculations. In this article, I will explore the various ways to use wildcards in Excel. So let’s dive in.

What is Wildcard in Excel?

In Excel, a wildcard is a special character or symbol that represents one or more unknown characters in a search or manipulation operation. Wildcards are used to perform flexible and pattern-based searches within data. They allow you to find matches or partial matches based on specified criteria rather than exact values.

The two commonly used wildcards in Excel are the asterisk (*) and the question mark (?). There’s another wildcard character, tilde (~) that’s used to treat asterisk (*) and question mark (?) as regular characters.

1. The Asterisk (*) Wildcard: Representing Multiple Characters

The asterisk (*) is a commonly used wildcard in Excel, representing any number of characters or a group of characters. It allows you to search for patterns or partial matches within your data. For instance,

• w* – looks for any word that starts with “w” followed by any characters, such as water, window, windmill,
• *w – looks for any word that ends with “w” like arrow, saw, new,
• *w* – looks for any word that contains “w” in any position, such as swing, aware, bowl,

2. The Question Mark (?) Wildcard: Representing a Single Character

The question mark (?) is another powerful wildcard in Excel, representing a single unknown character. It’s particularly useful when you’re unsure about the exact spelling or when there might be variations in your data.

For instance, if you have a list of names and you’re not sure about the correct spelling of a particular name, you can use the question mark wildcard to search for possible matches. Searching for “J?n” will find names like “Jon”, “Jan”, or “Jen”, giving you more flexibility in your data analysis.

• ? – Matches any single character. Example: “a?” matches “ab”, “ac”, but not “abc”.
• ?? – Matches any two characters. Example: “a??” matches “abc”, “axy”, but not “ab”.
• ??-??-?? – Matches any string in the format of two characters followed by a hyphen, then two more characters, another hyphen, and finally, two more characters. Example: “??-??-??” matches “ab-cd-ef”, but not “abc-def-ghi”.

3. The Tilde (~) Wildcard: Representing Wildcard Nullifier

The tilde (~) is used as an escape character to treat the following character as a literal character rather than a wildcard.

If you have a list of values that contain asterisks (*) or question marks (?) as part of the actual text, and you want to find those specific values, you can use the tilde wildcard as follows:

If you want to find the value “10*20” you can search for “10~*20” to treat the asterisk (*) as a literal character.

Similarly, if you want to find the value “A?B” you can search for “A~?B” to treat the question mark (?) as a literal character.

More examples:

• *~? – looks for data ends with a question mark, e.g. When?, How are you?, etc.
• *~** – looks for data containing an asterisk at any position, e.g. 5*5, *137*, 7-August-1996*, etc. Here, the first and third asterisks serve as wildcards, while the second asterisk specifically represents the literal asterisk character.

Use Wildcard in Searching in Excel

Wildcard characters allow you to perform advanced search operations throughout your data in Excel. In regular search operations, Excel returns all matching data. But for the following scenario, you can use wildcard characters to get your exact desired data. Here, specifying ???-??? on the Find what box, returns the exact desired data.

Use Wildcard in Filtering Data in Excel

Wildcard characters can also be helpful in filtering data within a large Excel dataset. Look at the ID column in the following dataset. Let’s say we want to filter all the IDs having only 4 characters. How can we do that? Well, it’s really easy if you know how to use the wildcard characters.

All you need to do is,

Step_1: Click on the filter drop-down arrow of the ID column.

Step_2: Type 4 question marks (?) in the search bar of the drop-down menu.

Now you will see Excel has filtered all the IDs having only 4 characters. Amazing, right?

Combining Wildcards with Other Criteria

In Excel, you can also combine wildcards with other criteria to create more advanced search or filter conditions. By using wildcards in conjunction with functions like COUNTIF, SUMIF, or VLOOKUP, you can perform calculations on specific subsets of data that match certain patterns. For instance, you can use the COUNTIF function with the asterisk wildcard to count the number of cells that contain a specific set of characters. This allows for greater precision and flexibility in your data analysis tasks.

Conclusion

In conclusion, wildcards are a powerful and versatile feature in Excel that can greatly enhance your data manipulation capabilities. By mastering the use of wildcards, you can unlock a whole new level of data analysis in Excel, making your tasks easier and more accurate.

Excel Glossary | Active Cell | Cell Address | Range | Function | Argument | Formula | Worksheet | Workbook