Why numbers copied from a website are stored as text in Excel
Webpages write numbers for people: "₹1,23,456", "4.6 out of 5", "(1,284)", "1.234,56 €", often with a non-breaking space inside. Excel keeps such cells as text, so SUM gives 0. Fix them with the formulas below, or export the page with a tool that turns each column into real numbers.
Signs that numbers are text
- They line up on the left of the cell, with a small green triangle in the corner.
- SUM returns 0 and sorting puts 100 before 20.
=ISNUMBER(A2)returns FALSE.
The usual causes, and fixes
| Cause | Example | Fix in Excel |
|---|---|---|
| Non-breaking space | "1 234" | =VALUE(SUBSTITUTE(A2,CHAR(160),"")) |
| Currency symbol or unit | "₹57,890", "675 sq ft" | Find and replace the symbol or unit with nothing, then Convert to Number |
| Comma as decimal mark | "1.234,56" | =NUMBERVALUE(A2,",",".") |
| Words around the number | "4.6 out of 5 stars" | =VALUE(LEFT(A2,FIND(" ",A2)-1)) |
| Brackets for counts | "(1,284)" | =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")) |
| Footnote marks | "1,428,627,663[1]" | =VALUE(LEFT(A2,FIND("[",A2&"[")-1)) |
For a whole column of plain text numbers, a quick trick also works: select the column, click Data › Text to Columns and then Finish. Excel reads every cell again as a number.
Dates have the same problem
"03/04/2025" is 3 April in most of the world and 4 March in the US. Excel guesses from your computer's settings and leaves the rest as text. Read the whole column the same way: in Power Query, change the column's type "Using Locale…" and pick the page's country.
Avoid the clean-up
NeatCells reads each column as a whole before exporting: it removes symbols and hidden spaces, understands lakh and crore, European separators and words such as "out of 5 stars", and puts the currency in the column name, "Price (₹)". Dates become real dates, read the same way for the whole column. When a value is unclear it stays as text, and any column can be kept exactly as the page shows it.
Questions
Why does SUM return 0?
SUM ignores text, and the cells hold text that looks like numbers. Convert them with VALUE or NUMBERVALUE, or with Data › Text to Columns › Finish.
What is CHAR(160)?
A non-breaking space. Webpages use it to keep numbers and units together; Excel does not treat it as a space, so TRIM alone does not remove it.
Does Google Sheets have the same problem?
Yes. Use =VALUE(SUBSTITUTE(A2,CHAR(160),"")) the same way, or Format › Number after removing symbols.