Why does Excel treat long numeric strings as scientific notation even after changing cell format to text

Solution 1:

Ah, memories of data munging back from when I did some massive number cross checking in Excel (never again).. When you punch in long numeric strings into Excel, try say, 12345678901234567890 (20 digits), Excel will generally convert it for you, meaning that the 20 digit number you've just tapped in has been cut back to be only about fifteen significant figures.

Note as well: the conversion is a truncation, as opposed to rounding. (12345678901234567890 is 1.23456789012346E+19, but excel will show you 1.23456789012345E+19)

This occurs at point of entry, so once it's in, any additional detail is lost, and so even if you tell Excel that you really meant that was text, and not a number, you're kind of out of luck, and hence why your first sequence doesn't work.

Solution 2:

This worked for me in Excel 2010. Just select the column, right click, Format cells, Custom and choose the option that says 0 (second option below General).

Solution 3:

Try this... this works for me, Excel 2013.

=""&a1

where a1 holds the numeric value. After, copy the column and paste to original column.

Solution 4:

I found that in Office 2010, if you format the column first, and then paste in the data, it will show the large numbers correctly.

Solution 5:

A single apostrophe ' before a number will force Excel to treat a number as text (including a default left align). And if you have errors flagged, it will show as a number stored as text error on the cell.