Excel’s Find feature uses the asterisk (*) and question mark (?) as wildcards for flexible searches. This causes a problem when you need to find cells containing those exact characters. Your search for * will match any text, and searching for ? will match any single character. This article explains how to override this default behavior. You will learn the correct syntax to search for literal asterisks and question marks in your data.
Key Takeaways: Searching for Wildcard Characters in Excel
- Find and Replace dialog (Ctrl+F): Use a tilde (~) before the wildcard character to search for it literally, such as ~* or ~?.
- Find All or Find Next: The tilde prefix also works for these actions to locate every instance of the literal character.
- Within Formulas: Searching for literal characters is essential when auditing formulas that use multiplication (*) or wildcards in functions.
How Excel’s Find Feature Interprets Wildcards
The Find tool in Excel is designed for pattern matching. The asterisk (*) represents any sequence of characters, including zero characters. The question mark (?) represents any single character. This is useful for finding text that follows a pattern, like all names starting with “Jo” (search: Jo*) or five-letter codes where the third letter is “B” (search: ??B??). However, this design means you cannot directly search for cells containing an asterisk or question mark as data. To perform a literal search, you must use an escape character. The tilde (~) is the designated escape character in Excel’s search syntax. Placing it before a wildcard tells Excel to treat the next character as a regular symbol, not a wildcard.
Steps to Find Literal Asterisks and Question Marks
Follow these steps to search for cells that contain the * or ? character as part of their content.
- Open the Find dialog
Press Ctrl+F on your keyboard. Alternatively, go to the Home tab on the ribbon, click Find & Select in the Editing group, and then choose Find. - Enter the search term with a tilde
In the “Find what” field, type a tilde (~) immediately followed by the character you want to find. To find an asterisk, type ~*. To find a question mark, type ~?. - Configure your search scope
Click the Options button to expand the dialog. Ensure “Within” is set to “Sheet” or “Workbook” as needed. Check that “Look in” is set to “Formulas” if you are searching within formula bars, or “Values” if searching for displayed results. - Execute the search
Click “Find Next” to jump to the first matching cell. To see all matches at once, click “Find All.” A list will appear at the bottom of the dialog showing every cell containing the literal character.
Using Find and Replace for Literal Characters
The same tilde syntax works in the Replace tab. This is useful for removing or changing wildcard characters in your data.
- Open the Replace dialog
Press Ctrl+H. Or, go to Home > Find & Select > Replace. - Enter the literal character to find
In the “Find what” box, type ~* or ~?. - Enter the replacement text
In the “Replace with” box, type the text you want to use instead. To delete the character, leave this box empty. - Replace the characters
Click “Replace All” to change every instance, or use “Find Next” and “Replace” to review each change individually.
Common Mistakes and Limitations
Searching for a Literal Tilde (~)
Because the tilde is the escape character, you must also escape it to search for it literally. To find a cell containing a tilde, type two tildes in the “Find what” box: ~~. The first tilde escapes the second, telling Excel to search for the ~ character itself.
Wildcards Not Working in “Look in: Values” Mode
When “Look in” is set to “Values,” Excel searches the results displayed in cells. If a cell shows “10*” but its underlying formula is “=10*5”, searching for ~* in “Values” mode will find it. However, if the cell’s underlying value is the number 50 (the result of 10*5), the asterisk is not part of the value and will not be found. For thorough auditing, search with “Look in” set to “Formulas.”
Confusion with Filter Wildcards
The AutoFilter feature in Excel tables uses different wildcard rules. When typing criteria directly into a filter dropdown, you do not use the tilde escape character. To filter for rows containing an asterisk, you simply type * in the search box. The Find dialog and the Filter search box have separate parsing rules, which can be a source of error.
Literal Search vs. Wildcard Search: Key Differences
| Item | Literal Character Search | Wildcard Pattern Search |
|---|---|---|
| Purpose | Find cells containing the exact characters * or ? | Find cells matching a flexible text pattern |
| Syntax in Find Box | Use a tilde prefix: ~* or ~? | Use * for any text, ? for any single character |
| Example Search Term | ~* finds cells with an asterisk | A* finds cells starting with “A” |
| Escape Character | Required: The tilde (~) itself is escaped as ~~ | Not used |
| Primary Use Case | Data cleanup, formula auditing, finding special symbols | Finding names, codes, or text with unknown segments |
You can now accurately locate asterisks, question marks, and tildes within your Excel worksheets. Use the tilde escape character in the Find dialog to override the default wildcard behavior. For a related skill, try using wildcards within the Find and Replace dialog to clean inconsistent data entries. Remember that pressing Ctrl+F and typing ~? is the fastest way to find all single question marks in your sheet.