Goals gone wild

Wildcards can help. These are symbols used to replace or represent one or more characters, typically for use in text strings.

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. It should be noted:

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

Various examples may be found in this month’s 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 I12 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 I18) is the one to use:

=SEARCH("C*a",G18)

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 first match can be found easily using the MATCH function:

MATCH(lookup_value, lookup_list, [match_type])

which returns the relative position of the first item in an unsorted list that (approximately) matches a specified value. The tilde ~ has been used to search for the actual wildcard characters in text strings. However, note the second example in cell H28: 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 H27, the formula would have returned an #N/A error instead.