Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to add more than 20 digits in an Excel cell.

The current format of the cell is general, it converts the number to an exponential format. I tried with a number format and accounting, but when I enter more than 15 digits it gets converted to 0's.

What are the recommended steps for stopping Excel from converting data to exponential Format for 20 digits when in the general format?

Example: 12345678901234567890

Excel converts it to 1.23457E+19 in general format.

Without using ' before the value, is there any other way to keep value same?

share|improve this question
1  
Do you really need to do calculations with that much precision? That's like trying to count how many hydrogen atoms you could line up along the earth's orbital radius. What practical difference does it make if you're out by a million or two atoms? –  Andrew Turner Jan 3 '12 at 13:37
    
I used to work on financial app. I had to store a product of 8 digits x 8 digits temporarily while calculating. 8 digit is not that uncommon, right? –  Kenji Noguchi May 29 '13 at 11:54
add comment

6 Answers

I had to have a large number in order to create a unique number for a website.

In the end I created the large numbers by using concatenate to add cells together. Example:

OL  2890000000  0000004 OL28900000000000004
OL  2890000000  0000005 OL28900000000000005
OL  2890000000  0000006 OL28900000000000006
OL  2890000000  0000007 OL28900000000000007
OL  2890000000  0000008 OL28900000000000008
share|improve this answer
add comment
  1. just copy your number from the cell
  2. format another cell(where to copy-destination cell) as text
  3. double click on the destination cell and paste in it.
share|improve this answer
add comment

When you don't need to calculate with these numbers, you can treat them as text, see the other answers. When you need to calculate with them, then it becomes difficult because Excel has only 15 siginificant digits. The possibilities I know are:

  1. Split the number in 2 (or more) cells. Put a part of the number in one cell and the remainder in another cell. The consequence is that you need to develop your own formules for calculations.

  2. Another solution is using add-in's. For a free one study the articles mentioned in http://www.excel-ticker.com/calculation-of-very-large-numbers-in-excel-part-5-add-in/

share|improve this answer
add comment

The reason is the limited precision that can be stored in a floating point variable. For a complete explanation you should read the paper "What Every Computer Scientist Should Know About Floating-Point Arithmetic", by David Goldberg, published in the March, 1991 issue of Computing Surveys.

Another, more accessible site, is Chip Pearson's site.

In Excel, the floating point type is Double which is a IEEE 64-bit (8-byte) floating-point number. These can display 15 digit precision (well sort of, see the MSDN article Excel Worksheet and Expression Evaluation).

share|improve this answer
add comment

I'm using Excel 2007 and I found a way to do what you're trying to do.

  1. Select the cells that you will fill with numbers with lots of digits;
  2. Find "Number Format" under the Home tab and choose "More Number Formats";
  3. Choose Custom and choose the second option. (Only the number 0)
share|improve this answer
    
The problem isn't that the numbers aren't displaying, it's that Excel is dropping them entirely by rounding. –  Allen Gould Aug 7 '13 at 21:04
add comment

Some numbers are displayed in exponential format if the column is too narrow and you can fix this by increasing the width of the column.

However, very large numbers in Excel are stored in floating point format and cannot be represented in Excel exactly. You may be able to override their display using cell formatting but the true values will still be stored and processed using floating point arithmetic, with its inherent limitations.

If you need to perform calculations with greater precision you need an application that supports arbitrary precision arithmetic.

share|improve this answer
    
By increasing width of column also number format in exponential only –  user111921 Jan 3 '12 at 5:04
    
@user111921, correct. If the number is very large it is stored in floating point format and must be displayed in exponential form, no matter how wide the column is. –  Mike Fitzpatrick Jan 3 '12 at 5:14
    
NUmber format is normal number there is no floating point.For normal number range after 15 digits value the value is changes to exponential format –  user111921 Jan 3 '12 at 5:19
    
@user111921, correct. If you enter a large integer into Excel it will be converted to floating point (with some loss of precision) and displayed in exponential form. It is unavoidable in Excel and most other applications unless they specifically support arbitrary precision arithmetic. –  Mike Fitzpatrick Jan 3 '12 at 5:23
    
To be precise, Excel 2010 doesn't support integers with more than 10 digits. Enter a number with 11 digits, or enter 9,999,999,999 and add one to it in a formula, and you end up with a floating point number. You can format it as an integer, but you still your low digits will be lost to rounding error. –  Isaac Rabinovitch Oct 8 '12 at 4:48
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.