If you had to work with Excel documents containing thousands of rows of unstructured data and needed a quick way of looking up entries without having to physically scan through several row entries, the Excel wildcards will come in handy for you. MS Excel wildcard characters are symbols that can be incorporated into Excel functions to create allowance for unknown information, be it character length or sentence/word structure. There are two well-known wildcard characters:

1. the question mark or “?” character – this is used to make allowance for unknown characters with known word length. For instance, if you were working with a data set that the original user had misspelled the word ‘bowels’ as ‘bawels’, buwels’, ‘bovels’, ‘bowils’ and you wanted to find every instance, however misspelled, you would use the expression ‘b????s’, indicating that you want to find a 6-letter word that begins with ‘b’ and ends with ‘s’ and has 4 random characters in between. You could also use the question marks before or after the known characters, e.g. ‘??w???’, ‘?????s’.

2. the asterisk or “*” character – this is a more flexible wildcard and allows you to make allowance for when the both the characters and word length is unknown. For instance, using the expression ‘*so*’ will return text like ‘lesson’, ‘smithsonian’, ‘blossom’, etc.

Back to the instance I opened the post with. Let’s say Column C contains a mixture of first names and last names and you wanted to pull out every entry that had ‘Smith’ as a last name. If the names were ordered First name + Last name, using the Filter function alone would require a laborious manual scan of all the entries to select the required references. However, you could create a wildcard reference in an adjacent empty column that would read as follows:

=VLOOKUP(“*Smith*”,C2:C2,1,FALSE)

This formula asks Excel to check C2 if it has the word ‘Smith’ in it, regardless of its position (it could have words before or after it) and if it does, to return the full cell entry (column index 1 means return the same name we are checking). If it does not find ‘Smith’ it returns the #N/A error. If you don’t want it to return the #N/A error you could wrap an =IFERROR() formula around the formula above and insert whatever value you wish to see in the ‘Value if error’ argument. The ‘table’ referenced in the =VLOOKUP() formula is a relative reference that shifts when you drag the formula down, so that the formula is analyzing only the immediate adjacent cell entry (in this example, C2) at any given time. Once you drag this formula down, Excel will bring up only the entries that have the word ‘Smith’ in them, and you can then filter this new column out and work on the desired row entries as you wish.