How to Filter Special Characters in Excel (An Easy Guide)

While filtering in Excel, no direct command can filter the special characters. In this particular situation, we had to apply a custom filter in different ways. In this article, we’ll cover those easy ways to filter special characters in Excel with sharp steps and clear illustrations.

What Is Special Character?

The non-alphabetic and non-numeric characters are called the special characters. the punctuation marks and other symbols on our keyboard like ~,&,?,>,*,&,$,@ are examples of special characters. Special characters are widely used in programming languages.

Using Custom Autofilter to Filter Special Characters in Excel

First of all, get introduced to our dataset which represents some person’s phone numbers. Take a look; there are some special characters within the numbers. Now, we’ll use the Custom Autofilter command to filter for specific special characters.

1. Applying Filter for Single Special Character

In our very first section, we’ll apply the Custom Autofilter of Excel to filter out only one kind of special character from the data.

Steps:

• First, select any cell from the dataset.
• Click as follows: Home > Sort & Filter > Filter.

Now, see the triangular filter icon available in every header of the dataset.

• Next, click on the filter icon of the Phone Number column.
• Then, from the drop-down menu, click as follows: Text Filters > Custom Filter.

From the image below, you also see that it is only showing the whole phone numbers; there are no options with individual special characters to filter by default.

• After that, select contains from the top-left dropdown box.

• Or, you can directly click as follows: Text Filters > Contains to open the Custom Autofilter dialog box with contains command at once.

• Later, always insert the Tilde character (~) before the special character that you want to search for in the next text box. I filtered out for Question mark character (?).
• Finally, just press OK.

Now have a look, it’s now showing all the cells with the ‘?’ character.

2. Filtering for Two Special Characters in Excel

You can search for two characters at once, which means it will filter those cells that have both two characters. We’ll just use the And operator in the Custom Autofilter dialog box.

Steps:

• First, follow the first four steps from the first section to open the Custom Autofilter dialog box.
• After that, select the contains command in two drop-down boxes on the left side.
• Then insert the two special characters in the two text boxes on the right side. Don’t forget to insert the Tilde character (~) before the special characters. Here, I filtered for Question mark character (?) and Asterisk (*).
• Finally, mark the And operator and press OK.

Now, see, it’s showing the cell that contains both the special characters.

3. Applying Filter for Either of Two Special Characters

The Custom Autofilter also has an Or operator; by using that we can filter for the cells that have at least one of two special characters.

Steps:

• Again, follow the first four steps from the first section to open the Custom Autofilter dialog box.
• Later, again select the contains command from the left two drop-down boxes and insert the special characters in the two right text boxes. Here, I filtered for the special characters- The question mark (?) and Hyphen (-).
• Lastly, just mark the Or operator and press OK.

Soon, you will get the cells that have the Question mark (?) or Hyphen (-) character.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to filter special characters in Excel. Feel free to ask any question in the comment section and please give me feedback.

<< Go Back to Characters | Learn Excel