Excel formatting for long numbers

Copper Contributor

When entering long numbers (ex: 9114902307224014945528), Excel converts this to "9.11490230722401E+21".  I have tried reformatting the cells and even opened a blank/new spreadsheet.  No difference.  What is happening and why?

3 Replies

@kpagenk 

Those values are larger than Excel can handle and display with full precision; see Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” 

I hope that Joe User will explain it in more detail.

Thanks!

@kpagenk  wrote: ``When entering long numbers (ex: 9114902307224014945528), Excel converts this to 9.11490230722401E+21``

 

If 9114902307224014945528 is really an ID, not a number that you intend to use in arithmetic expressions, it should be entered as text.

 

If you type the ID manually, format the cell as Text before you enter it.  Alternatively, type an apostrophe (single-quote) before typing the ID.

 

If you input the ID(s) by copy-and-pasting from another window, Excel usually changes the cell format to General and enters the data as a number.

 

Instead, I paste into Notepad and save to a text file first.  Then....

 

If you input the ID(s) from a text file (including a CSV file), do not open the file directly in Excel. 

 

Instead, use the Import Text File feature (click Data > Get External Data > From Text).  In the last (third) dialog box, select the column of IDs, then select Text under "Column data format".

 

-----

 

If 9114902307224014945528 is indeed a number that you intend to use in arithmetic expressions, you are SOL, unless you can use the Decimal data type in VBA.

 

(Or unless you want to use a third-party add-in.  I am not familiar with any of them.)

 

Note that once you enter 9114902307224014945528 as a number, Excel converts it to the binary approximation of 9114902307224010000000, which is different from the binary approximation of 9114902307224014945528.   IOW, Excel truncates (not rounds) to the first 15 significant digits when data is entered.