'%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 Cannot Convert Text Like 23.00 to a Number
Question details
The user is unable to convert text representations of numbers (such as "23.00") into actual numerical values in Excel, resulting in a formula error.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to use the VALUE or NUMBERVALUE functions on imported or scraped data that contains numbers formatted as text.
- Observed behavior
- The formulas return a #VALUE! error instead of the converted number, likely due to hidden, non-printing, or non-breaking characters within the cell.
Before applying advanced formulas, visually inspect your data for trailing spaces or imported quotation marks, and ensure your cell format is set to General or Number rather than Text.
Clean, Trim, and Convert Using Nested Formulas
Use a combination of CLEAN, TRIM, and SUBSTITUTE functions to strip hidden characters and non-breaking spaces before converting the text to a number.
Imported data often contains non-breaking spaces (CHAR(160)) and other non-printing characters that the standard TRIM function cannot remove. Combining these text-cleanup functions guarantees that the VALUE or NUMBERVALUE function can process the text string correctly without throwing a #VALUE! error.
Click on an empty cell adjacent to your problematic data (for example, cell B1 if your text is in A1).
Type the formula =NUMBERVALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),"")))) into the formula bar.
Press Enter. The cell should now display the correct numerical value (e.g., 23) instead of a #VALUE! error.
Click the small square at the bottom-right corner of the selected cell and drag it down to apply the formula to the rest of your data.
Identify and Remove Specific Hidden Characters
If standard cleaning formulas fail, manually identify the hidden character's Unicode value so you can explicitly remove it.
Remove Quotation Marks and Convert with a Double Unary
Use a double negative operator combined with SUBSTITUTE to quickly extract numbers enclosed in quotation marks.
Fix Formula Errors and Convert Text to Numbers in WPS Spreadsheet
WPS Spreadsheet seamlessly supports complex nested functions like VALUE, TRIM, CLEAN, and SUBSTITUTE. Easily process imported data, remove hidden characters, and convert text formats without encountering unexpected #VALUE! errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the problematic imported text.
- 2. Select the text cells: Click and drag to highlight the cells containing the text you want to convert to numbers.
- 3. Use the smart error indicator: If a small green triangle appears in the top-left corner of the cells, click the yellow warning icon that pops up.
- 4. Click Convert to Number: Select 'Convert to Number' from the drop-down menu for an instant, batch conversion.
- 5. Apply cleanup formulas if needed: For persistent hidden characters, use the =NUMBERVALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),"")))) formula exactly as you would in standard spreadsheet software.
Frequently Asked Questions
Why does Excel show #VALUE! when converting text to numbers?
This happens when the cell contains non-numeric characters, such as letters, hidden spaces, or non-breaking spaces (like CHAR(160)). The VALUE function cannot process these characters, resulting in a #VALUE! error.
What is the difference between the VALUE and NUMBERVALUE functions?
The VALUE function converts a basic text string that represents a number into an actual number. The NUMBERVALUE function is more advanced; it allows you to specify custom decimal and group separators, which is highly useful when processing international data formats.
How do I remove a non-breaking space in Excel?
The standard TRIM function cannot remove non-breaking spaces. Instead, you must use the SUBSTITUTE function combined with CHAR(160) to replace non-breaking spaces with an empty string. For example: =SUBSTITUTE(A1, CHAR(160), "").
