Sometimes when you are using a custom filter, or even an advanced filter, it can be difficult to get precisely the kind of records you want. By using wildcard characters you can fine tune your filters to retrieve all the data that you are after.

In Excel, you can use the question mark ? or the asterisk * as wildcard characters. A question mark will substitute a single character in a string, and an asterisk can substitute multiple characters in a string. For example, the string Ca? could be car, cat, can, cap and so on. The string Tele* could be television, teleportation, telemarketing, and so on.

To see how to use these wild card characters in a filter, take a look at the following Excel data list.

If you wanted a list of records pertaining to bears, you could use a wild chard character in your custom filter in the following way.

Invoke the custom filter box by selecting custom from the AutoFilter drop list in the Animal column.

Select equals and type * Bear as filtering criteria and click OK.

This is the resulting list for the custom AutoFilter. It contains every record from the list with the word Bear in the Animal field.

Here is another list and custom AutoFilter using the ? wild card, and the * wild card.

The first condition tests if Animal equals ?at.

The first condition is combined with a logical or to the second condition, which tests if Animal equals D*.

The or will return the records that meet either criterion.

The records that have a word starting with the letter D, and the records that have a three letter word ending in at will be shown.

(These criteria are based on the Animal field),

Here are the results of the wild card filter.

It should be noted that the wild cards do not have to appear at the beginning or end of a word. They can be used anywhere in a word to substitute the characters you want. For example,

ma*atics could represent the word mathematics with the * substituting for the letters t, h, e, and m.

Ro?e could represent Rope or Role or Rose or any other word of this form.

When using wildcard characters you should be clear on the distinction between the asterisk and the question mark. H??se is not the same as H*se, because H??se could be Horse or House but not Hose, while H*se could be Horse, House, or Hose.

You can combine both wildcard characters into one term as in S*e?, but the resulting records may be surprising because of the generality of the wild cards. (The example wild card term S*e? could be Sewer, Softer, Soften, or Salamander, to mention only a few of the possibilities).

The best way to find out what you can do with wild card characters is to experiment with them.