How to Convert Text with Currency Symbols and Hidden Spaces to Numbers in Excel
Question details
The user needs to convert text containing dollar signs, non-breaking spaces, and hidden web characters into recognizable numeric values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning data copied from websites that Excel fails to recognize as numbers due to unwanted characters.
- Observed behavior
- Excel treats the imported data as text instead of numbers because of hidden symbols, spaces, and currency formatting.
Identify the specific cell containing the problematic text and ensure you have an empty adjacent column to input your data cleaning formulas.
Clean Text Using the SUBSTITUTE Formula
This method is highly compatible with most Excel versions, including Excel 2021, to remove dollar signs and non-breaking spaces (CHAR 160).
Data copied from the web frequently contains non-breaking spaces, which Excel does not recognize as standard spaces. The SUBSTITUTE function can target both these specific characters and currency symbols, replacing them with empty strings.
Click on an empty cell next to your problematic data (for example, select cell C6 if your data is in B6).
Type the formula =--SUBSTITUTE(SUBSTITUTE(B6,CHAR(160),""),"$","") into the formula bar.
Press Enter to convert the text into a clean numeric value. Click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of the column.
Use REGEXREPLACE in Excel 365 for Advanced Cleaning
If you are using Excel 365, the REGEXREPLACE function provides a powerful way to remove a wide range of unwanted characters, including hidden spaces and zero-width characters.
Clean and Convert Data Easily with WPS Spreadsheet
WPS Spreadsheet offers robust formula support, allowing you to use functions like SUBSTITUTE to quickly clean web-imported data and convert text to numbers. It provides a familiar interface to process your datasets efficiently.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the text data you need to convert.
- 2. Select a target cell: Click on a blank cell next to the text you want to convert.
- 3. Enter the formula: Input the =--SUBSTITUTE(SUBSTITUTE(B6,CHAR(160),""),"$","") formula into the formula bar.
- 4. Apply to column: Press Enter, then drag the fill handle to apply the formula to the rest of your column.
Frequently Asked Questions
Why does Excel not recognize numbers copied from websites?
Numbers copied from websites often include non-breaking spaces (HTML entity or CHAR(160)) and zero-width characters that look like regular spaces but cause Excel to format the cell as text.
What does the double minus sign (--) do in the formula?
The double minus sign, also known as a double unary operator, forces the spreadsheet software to convert the text string resulting from the SUBSTITUTE or REGEXREPLACE functions into a proper mathematical numeric value.
Can I use Find and Replace to remove these hidden spaces?
Yes, you can use the Find and Replace dialog (Ctrl+H). In the 'Find what' field, you can type the specific character or hold Alt and type 0160 on your numeric keypad to find non-breaking spaces, then replace them with nothing.
Why is the REGEXREPLACE function returning a #NAME? error?
REGEXREPLACE is a newer function currently available only in specific updated versions of Excel 365. If you are using Excel 2021 or older, the function is not recognized, and you must use the SUBSTITUTE method instead.
