Excel Tip of the Week #292 - Wildcards

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a General User post in which we're taking a look at the use of wildcards in Excel.

What's a wildcard?

A wildcard represents an unknown character or characters.  It can be used to build text formulas or find-replace operations with greater flexibility than just having to type the exact search term or text you want.

There are two wildcards that Excel uses:

  1. ? - A question mark - represents a single unknown character
  2. * - An asterisk - represents any number of unknown characters

These can be used in text formulas or in the find/replace dialogue.

How do you use wildcards?

Let's take this dataset:

Let's start by building a "find" that identifies part numbers which are formatted as two characters, a dash, and then three more characters:

Or we could reformat the "Location" column to include only the letter / number code of the location, by removing anything before a space:

Turning to formulas, we could extract the date-formatted text from the delivery notes column:

Finally, we could make a formula to detect if the word "customer" appears anywhere in the General Notes column:

Putting the * marker at both ends of the formula lets Excel know that we care about the word appearing with any number of characters either side (including 0).  Note that we are using SEARCH rather than FIND, as FIND is case-sensitive.

How do I actually search for asterisks and question marks?

Because * and ? have these special roles, actually searching for one of them is a little trickier than normal.  But it's still possible - if you want to use a wildcard character in a find/replace or formula as itself, just put a tilde ~ before it:

You can download the workbook with all these examples at the bottom of this post.

Previous post - Making a set of PivotTables
TOTW index 
Next post - Revisiting layering conditional formats

Anonymous