You may have a spreadsheet where numbers are mixed with currency symbols or text units. This prevents you from performing calculations like SUM or AVERAGE. Excel treats these entries as text, not numeric values. This article explains how to use the Find and Replace tool to clean this data quickly.
Key Takeaways: Clean Numeric Data in Excel
- Ctrl+H (Find and Replace): Removes specific currency symbols like $ or € from selected cells to convert text to numbers.
- Wildcard asterisk (*) in Find what: Deletes variable text units like “USD” or “kg” that follow a number in a cell.
- Paste Special > Multiply by 1: A final step to force any remaining text-formatted numbers to become true numeric values.
Why Find and Replace is the Right Tool for This Job
When you import data or receive a file from another system, numbers often arrive with extra characters. A cell might show “$1,250.99” or “500 kg”. Excel cannot sum a column containing these entries. The Find and Replace feature searches for specific text strings and removes them or replaces them with nothing. This method is faster than manual editing and more precise than some text functions for simple, consistent patterns. You need to have your data selected in Excel before starting.
Steps to Clean Currency Symbols and Text Units
Follow these steps to strip unwanted characters from your numbers. Always work on a copy of your data first.
- Select Your Data Range
Click and drag to select the cells containing the numbers with symbols or units. You can select an entire column by clicking its header letter. - Open the Find and Replace Dialog
Press Ctrl+H on your keyboard. This opens the Find and Replace dialog box with the Replace tab active. - Remove a Specific Currency Symbol
In the “Find what” box, type the symbol you want to remove, such as $ or €. Leave the “Replace with” box completely empty. Click “Replace All”. Excel will remove every instance of that symbol from your selected range. - Remove Text Units After Numbers
For units like “USD” or “meters”, use a wildcard. In the “Find what” box, type a space followed by an asterisk ( *). This finds a space and any characters after it. Leave “Replace with” empty and click “Replace All”. This deletes the space and the unit text. - Convert Text to Numbers
After removing symbols, some numbers may still be left-aligned, indicating text format. In an empty cell, type the number 1. Copy that cell. Select your cleaned data range, right-click, and choose Paste Special. In the dialog, select “Multiply” and click OK. This math operation forces all values to become numbers.
Using Find and Replace for Leading Text
If your data has units before the number, like “EUR 100”, the process is similar. In the “Find what” box, type the unit text followed by a space (e.g., “EUR “). Leave “Replace with” empty and click “Replace All”. This removes the leading text and the space before the number.
Common Mistakes and Data Cleaning Pitfalls
Find and Replace Affects the Entire Worksheet
If you forget to select your specific data range before using Replace All, Excel will search the entire sheet. This can accidentally remove currency symbols from headers, notes, or other cells where they are needed. Always select your target cells first.
Numbers with Different Decimal Separators
European formats often use a comma as a decimal separator, like “1.250,99€”. Removing only the € symbol leaves “1.250,99”, which Excel may not recognize. You may need to use Find and Replace again to swap the comma and period: replace “,” with a temporary character, then “.” with “,”, then the temporary character with “.”.
Spaces Within Numbers Cause Errors
Some formats use spaces as thousand separators, like “$1 250.99″. Using the wildcard ” *” to remove a trailing unit will also delete the space within the number, creating “1250.99”. To avoid this, first remove the currency symbol, then use Find and Replace to remove only the specific unit text (e.g., “kg”) without a leading wildcard space.
Find and Replace vs. Text to Columns for Data Cleaning
| Item | Find and Replace | Text to Columns |
|---|---|---|
| Best Use Case | Removing consistent, known characters or words ($, €, kg) | Splitting data in a single cell into multiple columns |
| Handling Variable-Length Text | Excellent with wildcards (* and ?) | Requires a consistent delimiter like a comma or space |
| Speed for Large Ranges | Very fast, single operation | Requires a wizard with multiple steps per column |
| Converting Text to Numbers | Requires a separate Paste Special step | Has an option to set column data format in the final step |
| Risk of Overwriting Data | High if range is not selected | Lower, as it creates new columns by default |
You can now clean imported financial or measurement data for analysis. Use Ctrl+H to strip specific symbols and the asterisk wildcard for trailing text. For more complex cleaning involving inconsistent patterns, explore the TEXTSPLIT or VALUE functions. Remember to use Paste Special > Multiply by 1 as a final check to ensure all cleaned cells are true numbers.