You want to filter a table so that data rows for customers whose names begin with A are shown, but only the ones who ordered articles with 4-character article numbers. Because you do not want to enter exact filter criteria (i.e., complete customer names or article numbers), you want to filter the data using a Custom excel AutoFilter for text columns. This enables you to work with wildcard characters.In this learning module, you will be shown how to work with the Custom AutoFilters and how to use wildcard characters to make filter functions still more powerful.
HOW DOES EXCEL AUTOFILTER FOR TEXT WORK?
The Custom excel AutoFilter enables you to use filter criteria on columns containing text without fully specifying the filter criteria. This will become more clear as you continue reading…
First activate the AutoFilter function. Then, in the text column you wish to filter, click the filter arrow (in this example: the Customer column). In the the drop-down list that appears, select Text Filters. The submenu that now displays offers you a number of choices, all of which take you to the Custom AutoFilter dialog box in which you can specify filter criteria or, if you like, use wildcard characters to create complex filter criteria.
Equals -or- Does not Equal – These options permit you to filter a table so that the only rows in the filtered table that appear will be those for which the text in the column where your filter is applied matches or does not match the text you specify. In our example: you can filter the table to include only those data rows that contain certain customer names -or- using the other option, you can exclude table rows that contain a certain customer name.
Begins With -or- Ends With – These options permit you to filter a table so that rows will appear if the text in the column where you apply the custom text filter either begins with a certain character or ends with a certain character. In our present case, for example, one could use such an option to display all items that begin with the letter “M”.
Contains -or- Does Not Contain – These options enable you to filter a table so that only those table rows will appear if the text in the column where you apply the filter contains or does not contain (in any position) a certain character or characters. In our present case, for example, you could create a filter in the Customer column that allows all table rows that contain (or do not contain) “Co.” within customer names.
Custom AutoFilter – As do the options above, selecting this one also brings you to the Custom AutoFilter dialog box where you can manually enter all filter criteria, as described above, and combine them, if you like, using AND/OR operators.
Take a concrete example: If you want to display all customers whose names begin with A, click the command Begins With… and then, as indicated in the next illustration, type A into the input box. To apply the filter, click OK.
Excel AutoFilter for text :HINT (1)
Excel views quotation marks as normal characters. You should therefore not use quotation marks, unless such marks actually appear in the column for which you are creating the filter.
Now you want to allow just those rows in your table to display in which a 4-character Article Number was entered. To apply a filter to a column of text in your table such that only those rows will appear in the filtered table if cells in the column contain a specific number of characters, you have to use a wildcard character.
CAUTION when using excel AutoFilter for text
Wildcard characters can only be used for filtering columns containing text. They cannot be used in filters for columns that contain numbers or dates.
Wildcard characters are used only when you are specifying a Custom AutoFilter. More specifically, you wanted to create a filter to display table rows whose corresponding Article Number has exactly four characters. To do so, click the filter button on the Article Number column. Then, in the drop-down list, select the Text Filters option. In the submenu, click Equals. The Custom AutoFilter dialog box will then appear.
In Excel, there are two wildcard characters: the asterisk (*) and the question mark (?) . Each has a different role to play:
|?||…any single character||An entry of: Bar?on, would match Barron and Barton, etc.|
|*||…any number of characters||An entry of *east, would match Northeast and Southeast, etc.|
Going back to our example: if you enter ????, any first, second, third and fourth character would match, as long as there are indeed four consecutive characters. No cell with more than or fewer than four chararacters would match. Therefore, enter ???? now, and then click OK to apply the setting and close the dialog box.
As you can see, the filtered table rows that now appear all contain 4-character Article Numbers.
Excel AutoFilter for text: HINT (2)
Of course, as made apparent in the Custom AutoFilter dialog box, it is also possible to apply another criterion by using an AND/OR operation to further refine the filter. You will learn more about this in the learning modules: Custom AutoFilter for date columns and Custom AutoFilter for numeric columns.