How to Filter Special Characters in Excel – 3 Methods

What Is a Special Character?

Non-alphabetic and non-numeric characters are called the special characters. The punctuation marks and other symbols, like ~,&,?,>,*,&,$,@ are examples of special characters.

The dataset contains Names and Phone numbers. There are special characters within the numbers.

Use the Custom Autofilter to filter special characters.

Method 1 – Applying a Filter for a Single Special Character

Steps:

• Select any cell in the dataset.
• Click Home > Sort & Filter > Filter.

The triangular filter icon available in every header of the dataset.

• Click the filter icon in the Phone Number column.
• Click Text Filters > Custom Filter.

It shows the whole phone numbers; there are no options with special characters to filter by default.

• Select contains.

• You can also click  Text Filters > Contains to open the Custom Autofilter dialog box, and select contains.

• Enter the Tilde character (~) before the special character you want to search. Here, Question mark (?).
• Click OK.

All cells with ‘?’ are displayed.

Method 2 – Filtering Two Special Characters in Excel

Steps:

• Follow the first four steps in the first section to open the Custom Autofilter dialog box.
• Select contains.
• Enter the Tilde character (~) before the special characters. Here, Question mark (?) and Asterisk (*).
• Check And.
• Click OK.

The cell that contains both special characters is displayed.

Method 3 – Applying a Filter for Either of the Two Special Characters

Steps:

• Follow the first four steps in the first section to open the Custom Autofilter dialog box.
• Select contains and enter the special characters. Here, The question mark (?) and Hyphen (-).
• Check Or.
• Click OK.

Cells that have the Question mark (?) or Hyphen (-) will be displayed.

Download Practice Workbook

Download the free Excel workbook.

<< Go Back to Learn Excel