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

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).

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.

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.