numbers convert to formula in csv

[SteveJ]

I can't get a column of 16 digit numbers to transfer when converting the file
from an xls to a csv file. The cells in the csv appear to be a scientific
formula (5.19E+15, 4.27E+15, etc.). When actually clicking in the cell, the
number displays correctly except for the last digit which displays as a 0. I
think this might have something to do with the +15 that's in the formula
since the number that should appear is 16 digits long.

I've tried formatting the column in the xls file as text, general and number
(without decimal) and end up with a variety of results when saving and then
opening the csv.

 

[David Biddulph]

Numbers in Excel are limited to 15 significant figures. If you want a 16
digit number, you need to format the cell as text *before* entering the
'number', or precede the 'number' by an apostrophe to keep it as text.

Remember that if you save such a text string in a csv file it will be
correctly saved (as you can see if you open the csv with Notepad), but if
you merely open the csv with Excel, it will again be treated as a number, so
will be back to 15 significant figures. If you do want to open a csv with
Excel without it getting garbled, do so not with File/ Open but with Data/
Import External Data, and make sure that in the text import wizard you
specify the relevant columns as being text.

 

[SteveJ]

Problem solved. I had previously formatted the cells as text before saving
to csv and still had the problem, but I've just redone it and everything
worked as expected. I must have errored the first time I did it. Thank you
for your help.