General discussion


Excel - autoformatting number to formula

By Kate ·
I have a file that is acting kinda funny. Maybe I am just an idiot. :)

In a Microsoft Excel 2000 WS, I have a column that includes 16 digit numbers such as: 4366163039281060 (exampple). Excel is automatically converting to 4.36616E+15 (example). This is only after I convert to.csv file and open it in Excel. The actual .csv file is fine. This wouldn't be an issue, I am importing this to a program and it will be fine, but the executives do not understand this and want to see the file looking all pretty in Excel. If I save it to .xls, it looks fine. If I change the column to number format, it is fine until I save the .csv and reopen in Excel. If I open as .txt, it is fine as well.

I must be missing something very easy, but I have changed all of the settings I think may be effecting this, and I cannot get it to work.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by neilb@uk In reply to Excel - autoformatting nu ...

You wont get it to work. The csv file does not hold Excel cell formatting information so that the numbers are recognised as numbers and converted to the exponential format when you load he file.

Collapse -

by OnTheRopes In reply to Excel - autoformatting nu ...

I created an example like yours and it did the same thing, giving me a dialogue box saying that features for.cvs file types aren't compatible with .xls.

I then when to format, cells, number, and checked the "Use 1000 seperator (,)" box and it displayed correctly when re-opened as a .csv.

Collapse -

by zimonar In reply to Excel - autoformatting nu ...

When Excel is automatically converting to 4.36616E+15, you can convert them to numbers in one cell or range of cells at a time or in multiple nonadjacent cells or ranges of cells all at once; select the column, on the Tools menu, click Options. On the Error Checking tab, check the Enable background error checking box and check the Number stored as text box and click OK. On the worksheet, click the error button that appears, and then click Convert to Number.

Collapse -

by bschaettle In reply to Excel - autoformatting nu ...

Two issues here:

[1] As previously mentioned, the .csv format won't retain the formatting.

[2] -IMPORTANT- Excel cannot hold 16 digits of precision in a number format, only 15. These "numbers" will need to be formatted as text strings. Let me guess, they're credit card numbers? Excel is infamous for not being able to store credit card account numbers in a number format. If you try to do this, it will convert any digits beyond the 15th to zeros.

Collapse -

by gcheris In reply to Excel - autoformatting nu ...

Hi Kate,

To retain the calculation function format the column as a number and set the decimal places to zero. This method will automatically change the scientific notation to the 16 number look.

Collapse -

by parier In reply to Excel - autoformatting nu ...

I dont think your the idiot if you have to click three times to change a format in excel for executives....
oops - that was out loud...

Anyways, you could also link a copy of this spreadsheet from their desktop (properly formatted) to the CSV, this way they can open it anytime and see the date, correctly formatted.

Also, if the file is CSV, you can insert formulas before importing (if possible from source), such as

Column results will be:


Related Discussions

Related Forums