Goals Gone Wild

Still haven’t found what you’re looking for? It happens to the best of us and probably U2. Maybe Excel’s wildcards can help. By Liam Bastick, director with SumProduct Pty Ltd.

Query

Can you explain how wildcards work in Excel? I understand these may help me with my data analysis work.

Advice

Given each Excel spreadsheet can contain up to 1,048,576 rows and 16,384 columns, with each cell containing as many as 32,767 characters, that’s an awful lot of data you can trawl through.

Wildcards are symbols used to replace or represent one or more characters, typically for use in text strings. They were mentioned in passing in our recent article on Text Messages.

There are three principal wildcards in Excel:

• ? – The question mark is used as a replacement for any single character. For example, “C?MA” would find “CIMA” and “COMA”
• * – The asterisk is used as a replacement for any number of characters. For example, “C*am” would find “Cram” and “Cream”
• ~ – The tilde followed by one of the above two characters (i.e. ‘?’ or ‘*’) is used to search for these characters, that is so that you may differentiate the actual character from a wildcard. For example, :W*~?” would find “Why?”.

Wildcards will not work with all Excel functions. From Text Messages, it was noted that:

• FIND(Find_Text,Within_Text,[Start_Number]) is a search function which is case sensitive but does not allow wildcard characters; however
• SEARCH(Find_Text,Within_Text,[Start_Number]) is a search function which is not case sensitive but does allow for wildcard characters.

There are other functions that allow wildcards. These include (but are not limited to):

• AVERAGEIF / AVERAGEIFS: Returns the average (arithmetic mean) of all the cells in a range that meet one or more criteria
• COUNTIF / COUNTIFS: Counts the number of cells that meet one or more criteria
• DSUM: Excel database function, which allows summation based on one or more criteria
• LOOKUP / HLOOKUP / VLOOKUP: Looks up data based on criteria being met in the LOOKUP range
• MATCH: Looks up values in a list and returns its relative position
• SUMIF / SUMIFS / SUMPRODUCT: Adds the cells in one or more rows or columns specified by multiple criteria.

Examples

Various examples may be found in the attached Excel file. To show how the wildcards work, however, we use SEARCH(Find_Text,Within_Text,[Start_Number]) here. SEARCH() is a search function which is not case sensitive but does allow for wildcard characters, viz.

The first example in cell J15 highlights an important point: you must use the right wildcard for the situation. This formula searches for “C?a” in the text “Coca Cola”. This produces an error since nowhere in this text string is there an “a” or “A” two characters after a “C” or a “c”.

If you are actually looking for the first “C” or “c” in the text string which precedes an “a” or “A”, then the third example (cell J21) is the one to use:

The * wildcard can represent more than one character and therefore finds the criterion fulfilled at position 1 in the “Coca Cola” text string as there is a “C” in position 1 with a subsequent “a” in position 4.

Word to the wise

Care is needed when using wildcards, not only regarding how many characters the wildcard represents, but where you are looking too.

Consider the following example:

Here, the tilde ~ has been used to search for the actual wildcard characters in text strings. However, note the second example in cell H29: the MATCH criterion is “*~**”. The final asterisk is required to highlight that the sought after asterisk may not occur at the end of the text string. If it is omitted, as in the similar formula in cell H28, the formula would have returned an #N/A error instead.