'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel SUBSTITUTE CHAR(160) #VALUE! Error
Question details
Users encounter a #VALUE! error when attempting to use the SUBSTITUTE function alongside CHAR(160) to convert text containing non-breaking spaces into numbers.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Cleaning imported web data or system exports that contain non-breaking spaces (CHAR 160) so that the data can be calculated as numeric values.
- Observed behavior
- The formula returns a #VALUE! error because the application fails to recognize the cleaned string as a valid number, often due to lingering commas, standard spaces, trailing characters, or regional setting conflicts.
Before applying complex formulas, verify your system's regional settings for decimal and thousands separators, as mismatching commas or periods can prevent text from converting to numbers.
Use the Double Unary or VALUE Function to Convert Text
This is the most direct method to replace non-breaking spaces and force the resulting clean text into a numerical value.
Often, data pasted from websites includes hidden non-breaking spaces (represented by CHAR(160) in Excel). Removing them is only the first step; you must also explicitly convert the result from text to a number.
Click on an empty cell where you want the cleaned, numerical data to appear.
Type the formula =--SUBSTITUTE(B2,CHAR(160),"") and press Enter. The double minus sign (--) forces the text output to become a number.
If you prefer not to use the double minus, you can achieve the exact same result by wrapping the formula in the VALUE function: =VALUE(SUBSTITUTE(B2,CHAR(160),"")).
Remove All Commas, Spaces, and Trailing Characters
Use this nested formula approach when your data contains a mix of standard spaces, commas, and trailing characters that a simple CHAR(160) replacement cannot fix.
Clean and Convert Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides powerful data cleaning capabilities and full compatibility with advanced nested formulas like SUBSTITUTE and VALUE. Easily remove non-breaking spaces and troubleshoot #VALUE! errors in a free, lightweight, and user-friendly interface.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the data you need to clean.
- 2. Enter the formula: Select a blank cell and input =--SUBSTITUTE(A1, CHAR(160), "") to strip out the non-breaking spaces and convert the text to numbers.
- 3. Fill down the column: Click and drag the small square handle at the bottom right of the cell to apply the formula across your entire dataset instantly.
Frequently Asked Questions
What is CHAR(160) in spreadsheet formulas?
CHAR(160) represents a non-breaking space. It is very common in data copied from web pages or imported from external databases. Standard TRIM functions often fail to remove it, which is why SUBSTITUTE must be used.
Why does my formula return a #VALUE! error after using SUBSTITUTE?
The #VALUE! error occurs when you try to convert the cleaned text into a number (using -- or VALUE), but the application still doesn't recognize it as a valid number. This is usually caused by leftover commas, standard spaces, or mismatched regional settings for decimal separators.
What does the double minus (--) do in the formula?
The double minus, known as the double unary operator, coerces text representations of numbers (or boolean TRUE/FALSE values) into actual numeric values so that the spreadsheet can perform mathematical calculations on them.
How do I check my system's regional number settings?
In Windows, you can check this by going to Control Panel > Clock and Region > Region > Additional settings. Here you can see exactly which characters your computer expects for the decimal symbol and digit grouping (thousands) symbol.
