Wildcards

Excel provides three special characters called Wildcards that you might find really helpful when making criteria for text filters, and when you're searching and replacing content: ?, *, and ~.

• ? can be used as a replacement for any single character, i.e. searching for b?wl might return bawl or bowl.

• * can be used as a replacement for any number of characters, i.e. searching for North* might return Northwest or Northeast.

• ~ followed by any of the two other wildcards could be used to include those wildcards as a regular character, i.e. searching for Dogs~? will return Dogs?.

A wildcard’s greatest use comes in the form of allowing the user to search for partial matches of values. There are two major uses for this in Excel: when making criteria for text filters & when searching and replacing content.

• These two purposes are more similar than you might think. When a filter applies criteria to the selected data, it searches for and returns values that might match with the given criteria. Similarly, when you use Find & Replace, Excel searches for and replaces values that might match with the given criteria.

• You can extend this same logic to use wildcards for anything in Excel that might search for a string (i.e. complex formulas, conditional formatting). 

When using Filters:

• Wildcards can be used in both AutoFilter and Advanced Filter to modify criteria so that it may return strings partially matching the criteria.

• For example, say you have a table containing the sales of thousands of different items across many different regions. 

• You want to extract data for sales that came from the Americas but you don’t want to go through and click through them all tediously. You can just type *America* in the search bar and it’ll just leave regions within the Americas. 

• Note that *America won’t work because it’ll just look for strings that end in America.

• When using Advanced Filter, it’s basically the same thing. You can type partial strings under column headers using wildcards to fulfill the same purpose. 

• For example, using the same data, you would like to extract the item data from any of the -stan countries.

• 

• Create the following criteria range:

• Apply an Advanced Filter on the data using that criteria and you should arrive at such:

Note that wildcards only replace TEXT values. You CANNOT use wildcards to replace Dates or Numbers.

When using Find & Replace:

• As expressed, wildcards allow the user to search for strings that might match partially with their wildcard embedded strings. When using Find & Replace, you can use wildcards to search for strings as such.

• Ex. You have a table of apples sold by stores from different sections of the building (i.e. apples sold from the NorthWest sector, SouthWest sector, etc.). For one reason or another, the people that put in the data weren’t very consistent in their spelling and described SouthWest as South-West, South West, SouthWest, etc. 

• You want to replace every South…West to SouthWest. You can do that by pressing Ctrl - H to open Find & Replace or going to Home - > Editing - > Find & Select - > Replace.

• Fill the textbox to the right of Find what: with South*West and the textbox to the right of Replace with: with SouthWest. 

• Click Replace all and you should arrive at such:

So yeah, that’s pretty much everything you have to know for Wildcards in Excel. If you’d like to know more about Filters and Advanced Filtering you can check out my blog entries here and here. Thanks for reading my blog; see you next time : - )