Help with troublesome .CSV fields!

[markpruett]

Hello!
I have a spreadsheet with just one column of numbers that I will use to
upload to my website for a particular purpose. However, when a number in a
field exceeds 11 digits and I try to save as a .csv, the number is converted
to a formula. For example: "222222222222" is converted to "2.2222E+11".

I need the numbers in the string of digits and cannot have it made into a
formula because when it is uploaded is will be read as the formula and not
the string of digits.

Please let me know what I can do about this. Any response is appreciated.

 

[Alojz]

Hi, I guess ur number is displayed as 2.2222E+11 in excel file already,
meaning u see it in scientific format in Excel. Format it as number, if
necessary, widen the column. Then save in csv file type. Other thing is, that
preciseness of any number cannot be bigger than 15 digits (in Excel2007,
given by its technical limitation). E.g. even though the max positive number
can be 9,99999999999999E+307 only first 15 digits are original digits, the
rest is rounded down, using ur example, you cannot have in excel
2.222.222.222.222.229, but only 2.222.222.222.222.220.

HTH
Alojz

 

[Alojz]

one more remark: 2.2222E+11 is not formula, is scientific notation 2.22*10^11
(2.22 times 10 powered on 11)

 

[David Biddulph]

Have you actually looked at the text in the CSV file (using something like
Notepad) to see what is in there? My guess is that it is stored in the same
way that it was displayed in the Excel file from which you saved it. I
would guess further that you then used Excel to open the CSV file, and Excel
has decided how to display the number (which is as scientific notation, not
as a formula). If so, you can tell Excel to format the cell as number with
zero decimal places, or you may find it better not to OPEN the CSV file with
Excel but to use Data/ Import external Data, and tell it to treat the
relevant columns as text so that it doesn't get reinterpreted.

In general, after you've saved as CSV you shouldn't rely on Excel to tell
you what is in there. CSV is a text format, so read the text.