.csv file shortens numbers with "E+"

**BPSJACK** · 06-27-2012, 10:40 AM

Hi,

I have a csv file which contains some long numbers, excel seems to want to abbreviate them. Is there any way to override this so this does not happen when the file is opened.

For example in my current file the a cell containing 230702063986 will show up as "2.307E+11". I've tried changing the cell format to a custom format which does solve the problem, however when attempting to save it then says the file now contains features which are not compatible with CSV.

Any ideas?

**NBVC** · 06-27-2012, 10:52 AM

if the numbers are longer than 15 digits, Use Data|Text to columns to format the columns as text (3rd window in text to columns wizard).

if they are less than or equal to 15 digits, try formatting as Number with 0 decimals.

**BPSJACK** · 06-28-2012, 05:50 AM

This does work. However when you save it and try and reopen it, it's back to the general format.

What really gets me is that it shortens 230702063986 to 2.307E+11, but in the cell directly next to it I have 1VW48047HY988773R which appears unabbreviated. Madness.

**martindwilson** · 06-28-2012, 05:57 AM

no its not madness 1VW48047HY988773R is text ,230702063986 is a number two different things
but unfortunately when saved as csv , text formatted numbers and real numbers look the same
this is opened with notepad

Formula:

Please Login or Register  to view this content.

so when you open with excel it sees it as a number format.
however if you rename the .csv to .txt then open with excel you can set the field to text in the wizard