When Excel treats numbers as text, formulas ignore them even though they look numeric. They may align left, show a green triangle, refuse to total, or make SUM return 0.
Does this affect you?
Use this if numbers pasted from a bank export, website, CSV, PDF, or accounting tool will not calculate in Excel for Microsoft 365, Excel 2021, or Excel 2019 on Windows or Mac. Changing the cell format alone often does not convert the existing text values.
Use the error-checking button
This is the fastest fix when Excel shows the green triangle.
- Select the affected cells.
- Click the yellow warning icon beside the selection.
- Choose Convert to Number.
- Check that the values move to the right side of the cells and formulas start calculating.
If the icon does not appear, open File > Options > Formulas, enable background error checking, and make sure Number stored as text is checked.
Multiply by 1 with Paste Special
Use this when Excel does not show the warning but values still behave like text.
- Type 1 in an empty cell.
- Copy that cell.
- Select the text-numbers.
- Open Paste Special.
- Under Operation, choose Multiply, then click OK.
- Delete the helper cell after the conversion.
Use Text to Columns
This forces Excel to re-parse a whole column.
- Select the column or range.
- Open the Data tab and choose Text to Columns.
- Choose Delimited.
- Click Next twice without selecting delimiters.
- Click Finish.
More control
Use a helper formula when you need the original untouched
In a new column, use =VALUE(A2), fill down, then copy the results and use Paste Special > Values to replace formulas with real numbers.
Remove hidden web characters
Web data often includes non-breaking spaces. Use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),””))) to strip them before conversion.
Check decimal and thousands separators
If the source uses commas for decimals or periods for thousands, adjust Excel separators under File > Options > Advanced or clean the characters with Find and Replace before converting.
Confirm the fix
Select the converted range and look at the Excel status bar. If Sum shows the expected total instead of 0, the cells are real numbers.
Sources
- Microsoft Support – Convert numbers stored as text to numbers (2025)
- Microsoft Support – Correct a formula error and other troubleshooting tips (2025)
- Microsoft Support – VALUE function (2025)