When searching for text in Microsoft Excel using Find and Replace or filters, users may sometimes notice that searches containing an asterisk (*) or percentage (%) do not return the expected results. This happens because Excel treats certain characters as wildcards, which changes how the search function interprets them.
In Excel, the asterisk (*) represents any number of characters, and the question mark (?) represents a single character. As a result, when you try to search for these characters literally, Excel may interpret them as wildcards instead of normal text.
If you are unable to search for a file or cell content containing an asterisk (*) or percentage (%) in Excel, follow the methods below.
How to Fix Unable to Search for Asterisk (*) or Percentage (%) in Excel
Work through the following solutions to search for these characters correctly.
1. Use the Tilde (~) Character Before the Wildcard
Excel allows you to search for wildcard characters by using the tilde (~) as an escape character.
- Open your Excel workbook.
- Press Ctrl + F to open the Find dialog.
- In the Find what field, type: ~*
- Click Find All or Find Next.
This tells Excel to treat the asterisk as a normal character instead of a wildcard.
Similarly, to search for a question mark:
~?
The tilde ensures Excel searches for the literal symbol.
2. Use Filters to Locate Special Characters
You can also use Excel filters to find values containing special characters.
- Select your dataset.
- Go to the Data tab.
- Click Filter.
- Click the dropdown arrow in the column header.
- Choose Text Filters > Contains.
- Enter the search term with the tilde escape character if needed.
Filters can help locate entries containing wildcard characters.
3. Check the “Match Entire Cell Contents” Option
Sometimes Excel may fail to find characters due to search options.
- Press Ctrl + F.
- Click Options in the Find dialog.
- Check or uncheck Match entire cell contents depending on your search requirement.
- Try searching again.
Adjusting this option can change how Excel interprets search results.
4. Ensure the Correct Search Scope Is Selected
The Find tool can search within a sheet or the entire workbook.
- Press Ctrl + F.
- Click Options.
- Under Within, choose either:
- Sheet
- Workbook
- Perform the search again.
Using the correct scope ensures Excel searches the intended area.
5. Check for Hidden Characters or Formatting
If the search still fails, hidden characters may be present.
- Select the affected cells.
- Copy the value into another cell.
- Use the LEN() function to check the number of characters.
- Remove extra spaces using the TRIM() function if necessary.
Cleaning the data may help Excel locate the correct values.
6. Use Advanced Search Formulas
You can also use formulas to locate special characters.
Example formula:
=SEARCH("*",A1)
This can help detect whether the character exists in a cell.
Note that formulas may also treat wildcards differently, so escaping characters may be necessary.
Conclusion
If you are unable to search for an asterisk (*) or percentage (%) in Excel, it is usually because Excel treats certain characters as wildcards during searches. In most cases, using the tilde (~) escape character before the symbol allows Excel to search for it as a normal character.
By adjusting the Find settings, using filters, or cleaning cell data, you can easily locate entries containing special characters and improve your search results in Excel.
