data is corrupted when I import text file into Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tommy - 27 Jun 2007 14:32 GMT

I am opening a tab-separated text file into Excel. Everything is fine except
one column of data gets translated into some sort of format. For example, the
number "9612019505367510380337" gets translated into "9.61202E+21". I have
seen this other times with other folk's files. And I just expand the column
and the data appears correctly. However that does not work in this case. Any
information on why this is happening or how to fix it would be appreciated.

Reply to this Message

ShaneDevenshire - 27 Jun 2007 20:26 GMT

Hi,

Excel can only retain 15 decimal for numbers. You need to bring the number
into a text field. If you are using the import wizard, on the last step
select the field in the preview area and then click the Text option button.

Numbers displayed as 1.234E16 are in scientific notation, this occurs
because the data is too large to fit in the cell. You can widen the column
but you will also need to set the format to General or Number after you do
this. However, this won't solve the problem of more than 15 digits.

Signature

Cheers,
Shane Devenshire

> I am opening a tab-separated text file into Excel. Everything is fine except
> one column of data gets translated into some sort of format. For example, the
> number "9612019505367510380337" gets translated into "9.61202E+21". I have
> seen this other times with other folk's files. And I just expand the column
> and the data appears correctly. However that does not work in this case. Any
> information on why this is happening or how to fix it would be appreciated.

Reply to this Message