You may need to clean data by removing unwanted characters like asterisks, parentheses, or currency symbols from your Excel cells. Manually deleting each one is slow and impractical for large datasets. Excel’s Find and Replace feature can remove these characters in bulk. This article explains how to use Find and Replace to delete specific characters, including wildcards for patterns.
Key Takeaways: Deleting Characters with Find and Replace
- Ctrl+H (Find and Replace dialog): Opens the tool to search for specific text and replace it with nothing to delete it.
- Using the tilde (~) character: Allows you to find and delete wildcard characters like the asterisk (*) or question mark (?).
- Replace All command: Instantly removes every instance of the specified character or pattern from your selected range.
How Find and Replace Works for Deleting Text
The Find and Replace tool in Excel searches for text you specify and replaces it with new text. To delete characters, you tell Excel to replace them with an empty string. You can target a single character, a sequence of characters, or a pattern using wildcards. This method works on values in cells but does not alter formulas. The changes are permanent once you save the workbook, so it is wise to work on a copy of your data.
Steps to Delete Specific Characters
Follow these steps to remove unwanted characters from your worksheet.
- Select your data range
Click and drag to select the cells, columns, or rows containing the characters you want to delete. To process the entire sheet, click the triangle at the intersection of the row and column headers. - Open the Find and Replace dialog
Press Ctrl+H on your keyboard. You can also go to the Home tab, click Find & Select in the Editing group, and then choose Replace. - Enter the character to find
In the Find what box, type the exact character you want to delete, such as a dollar sign ($) or a hyphen (-). - Leave the Replace with box empty
Ensure the Replace with box is completely blank. This tells Excel to replace the found character with nothing, effectively deleting it. - Execute the replacement
Click Replace All. Excel will show a message confirming how many replacements were made. Click OK to close the message.
Deleting Wildcard Characters
The asterisk (*) and question mark (?) are wildcards in Find and Replace. To delete these literal characters, you must prefix them with a tilde (~).
- Open Find and Replace
Select your data and press Ctrl+H. - Use the tilde escape
To delete an asterisk, type ~* in the Find what box. To delete a question mark, type ~?. Leave the Replace with box empty. - Run the command
Click Replace All to remove all instances of that specific wildcard character.
Common Mistakes and Limitations
Find and Replace Deleted Too Much Text
If you use an asterisk (*) alone in the Find what box, it means any sequence of characters. Replacing it with nothing will erase all cell content. Always use the tilde (~*) to find a literal asterisk. Test on a small data selection first by clicking Find Next before using Replace All.
Characters in Formulas Are Not Removed
Find and Replace works on the displayed cell value, not the formula’s source code. To remove a character from within a formula, you must edit the formula bar directly or use a helper column with the SUBSTITUTE function.
Spaces or Non-Printing Characters Remain
Sometimes data contains non-breaking spaces or other invisible characters. These may not delete with a standard space. Use the CHAR function to identify the character code, then use Find and Replace with that code. For example, CHAR(160) represents a non-breaking space.
Find and Replace vs. SUBSTITUTE Function
| Item | Find and Replace | SUBSTITUTE Function |
|---|---|---|
| Operation Type | Manual, one-time action on cell values | Formula-based, creates a new value dynamically |
| Permanence | Directly alters the original data | Original data remains unchanged |
| Best For | Cleaning imported data or quick bulk edits | Ongoing data transformation within a workflow |
| Pattern Matching | Supports wildcards (*, ?) for flexible finds | Requires exact text; no wildcard support |
| Automation | Must be run manually each time | Updates automatically if source data changes |
You can now clean datasets by removing unwanted punctuation, symbols, or wildcard characters. Use the Replace All command for quick, bulk deletions across columns. For a more advanced technique, combine Find and Replace with the TRIM function to clean extra spaces after removing characters.