How to filter data by containing asterisk or other special characters in Excel?
When working with Excel, asterisks (*) and question marks (?) typically serve as wildcard characters in search and filter features. This can lead to difficulties if you need to filter for actual asterisk or other special character content within your cells—since by default, Excel interprets these as patterns rather than literal values. Filtering for literal asterisks or symbols requires some specific handling to ensure your data filter works as needed. In this guide, you will learn several practical ways to filter data that contain actual asterisks or special characters in Excel, discuss where each approach is most suitable, and learn tips, parameter notes, and troubleshooting steps along the way.
Filter data by asterisk or other symbol with Filter
Filter data by special symbol with Super Filter
Excel Formula: Filter by asterisk using helper column
Filter data by asterisk or other symbol with Filter
Suppose you have a range of data as shown in the screenshot below, and you want to filter out the phone numbers that actually contain an asterisk "*" in the TelePhone column. Because Excel treats "*" as a wildcard that matches any sequence of characters, filtering for a literal asterisk or symbol works a little differently. The instructions below detail how to achieve this, and these steps can be adjusted for other special characters as needed.
1. Highlight the range of your data. Then navigate to the Data tab and click the Filter button. This will add filter arrows to each of your header columns. See screenshot:
2. Next, click the filter drop-down arrow for the column you want to filter (here it would be TelePhone), choose Number Filters or Text Filters (depending on your data type), and select Custom Filter to open the custom dialog. See screenshot:
3. In the Custom AutoFilter window, pick contains from the dropdown menu. To filter for a literal asterisk, you must type ~* (a tilde before the asterisk) in the criteria box. The tilde "~" is used in Excel to indicate that the following character should be interpreted literally, not as a wildcard. See screenshot:
4. Click OK and Excel will now show only the rows where the selected column contains a true asterisk character—not just any character. See the result below:
If you wish to filter for other special characters such as a literal question mark (?), which is used in Excel as a wildcard for "any one character", use the same method but enter ~? in the filter box. Again, "~" tells Excel to treat it as a plain character, not a wildcard:
Notes & Tips:
– Always use "~" as an escape character before a wildcard when searching for the actual character.
– The Custom AutoFilter approach is suitable for smaller lists or ad-hoc filtering needs.
– If your column contains both numbers and text, switch the filter to "Text Filters" to avoid errors.
– Filtering by wildcards in Excel is case-insensitive and regional settings may affect how special characters are matched.
Common Issues:
– If you see no results, check if there are truly any cells with the literal character, or if it’s being used as part of another formula.
– Some older versions of Excel may use different wording in filter menus, but the logic is the same.
Applicable Scenarios & Analysis:
– This method is most effective for quick, interactive filtering and smaller datasets.
– It is not dynamic: if new data is added or removed, you’ll need to re-apply the filter.
Filter data by special symbol with Super Filter
If you use Kutools for Excel — an advanced Excel add-in — you can leverage its Super Filter feature to more flexibly filter by specific characters, including literal asterisks and a range of other special symbols.
You can free install Kutools for Excel and try the Super Filter feature by following the instructions below. This option can be especially useful when you need to define more complex filtering conditions that aren't possible with Excel's built-in filter dialog, and it works effectively for large datasets as well.
After installing Kutools for Excel, please proceed as follows:
1. Click Kutools Plus > Super Filter. See screenshot:
2. In the Super Filter pane that appears, click 
to select your data range. Make sure to check the Specified box to lock your selection and prevent it from changing. See screenshot:
Tip: You can drag the edge of the pane to reposition or resize it for better viewing.
3. Next, click the first underline in the first condition group to bring up the criteria selection box. See screenshot:
4. In the drop-down list, choose the column you want to filter. Use the next drop-down to select Text, followed by Contains (or other criteria as needed). Type ~* (the tilde and asterisk) in the final text box to indicate a real asterisk. Click OK to save the settings. See screenshot:
5. Click Filter at the bottom of the Super Filter pane to instantly view only the rows containing an actual asterisk character in your selected column.
To filter for other literal special characters such as a question mark (?), input ~? in the corresponding text box after "Contains". See screenshot:
With Super Filter utility, you also can handle these:
Filter by multiple criteria or wildcard in Excel
Notes & Tips:
– Super Filter is highly recommended for large datasets or when you need to build advanced filter logic (such as combining multiple conditions with AND/OR logic).
– Using "~" before a special character applies the literal filter in Super Filter, similar to Excel’s built-in filter.
– If your workbook has lots of filters or formatting, always double-check your selection in the filter pane.
Common Issues:
– Make sure the data range selection in Super Filter matches your intended table.
– If the filter doesn’t return expected results, verify your filter logic and confirm that the characters are present in the data.
Applicable Scenarios & Analysis:
– The Super Filter approach is efficient for batch filtering, frequent filter changes, or complicated filter combinations.
– Especially suitable for business/finance users handling complex data analysis tasks in Excel.
Excel Formula: Filter by asterisk or special character with a helper column
When Excel’s built-in filters are not sufficient, or when you want a dynamic and easily updateable solution, creating a helper column with a formula is very practical. This method works well for both large datasets and ongoing data entry situations. By using the ISNUMBER and SEARCH functions, you can flag rows that contain an asterisk or any other special symbol, then filter or sort the data based on the results.
This approach has the advantage that new rows will be automatically evaluated as they are added. It is also easy to adapt for other substrings or special characters.
Example: Assume your data is in range A2:A10 and you want to check for an asterisk "*" in each cell.
1. In the first cell of the helper column (e.g., B2), enter the following formula:
=ISNUMBER(SEARCH("~*", B2))This formula returns TRUE if the cell in column A contains an asterisk, and FALSE otherwise.
Note: Within the SEARCH function, the asterisk is not considered a wildcard in this context; it looks for the actual character.
2. Press Enter to apply the formula. Then, copy the cell B2 down alongside your data range by dragging the fill handle or double-clicking it.
3. Use Excel's Filter feature:
- Select your entire table, including the helper column.
- Click Data > Filter to add the filter arrows.
- Click the dropdown on the helper column and check only TRUE to display rows with an asterisk.
To filter for other special symbols: Replace "*" in the formula with the symbol you need, such as "?" for a question mark:
=ISNUMBER(SEARCH("~?",A2))Tips and Precautions:
- If your data is case-sensitive, use
FINDinstead ofSEARCH.SEARCHis not case-sensitive. - If your data may contain blanks, wrap the formula with
IFERRORto avoid errors:=IFERROR(ISNUMBER(SEARCH("~*",A2)),FALSE) - This solution is ideal for frequently updated datasets, or for filtering by multiple or changing special characters.
Advantages & Disadvantages:
+ Dynamic and automatically updates with new data.
+ Can be combined with conditional formatting, COUNTIF, or advanced analysis.
– Requires additional column.
– May be less convenient for very simple one-time filtering scenarios.
General troubleshooting and summary tips:
– Always double-check whether your special character is being used as a wildcard or plain text in your context.
– If the character is not found, verify by using LEN or SEARCH to confirm its presence.
– When using formulas, wrap with IFERROR to avoid #VALUE! if cells are empty or malformed.
By using any of these approaches, you can reliably filter for asterisks, question marks, or other special symbols in your data—enabling more precise searches and custom analysis structures when regular wildcard support becomes an obstacle. Choose the method that best fits your workflow, whether for one-time filtering, dynamic ongoing lists, or automated batch editing.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in