up vote 1 down vote favorite
share [g+] share [fb]

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.

Please recommend 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.

Excel converts it to 12345678901234500000 in number & accounting format.

link|improve this question
2  
If you want to keep it as text, just put an ' before it, e.g. '1234567890123456789 – Damien_The_Unbeliever Feb 11 '11 at 8:01
Just to note since this is popping up again, the issue here is the number of digits of precision supported by Excel for numeric cells. As noted in an answer below, that number is 15. There's some additional detail here: en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel but basically, if you're dealing with that many digits of precision you're going to be using specialized software or writing it yourself. – fencepost Apr 23 at 19:06
Also possibly of note, a commercial package (32-bit, dirt cheap, and with a trial version with popups): precisioncalc.com/xlprecision.html . If this lets you continue to use a familiar tool (Excel) and avoid other custom code, it may be worthwhile. For up to 25 digits, it's $5. Note that this may leave you with non-portable spreadsheets. – fencepost Apr 23 at 19:13
feedback

migrated from stackoverflow.com Feb 12 '11 at 21:21

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote

All numbers are stored internally by Excel as 15 digit floating-point numbers. If you want more than 15 digits you have to use Text rather than numbers, and so you will not be able to do arithmetic calculations with the number. You can make Excel store the number as text by putting an ' before it, as Damien says, or by formatting the cell as Text.

link|improve this answer
feedback
up vote 0 down vote

This probably belongs in Super User or some other forum.

I know absolutely nothing about Excel, but I believe you can write Visual Basic code for it, right? If so, and if nothing else works, you could always split the numbers up into two cells (one for digits 1-14, the second for digits 15-29): Cell[i]A and Cell[i]b.

Sum12B = Cell1B + Cell2B
// Detect a remainder in the sum via modular division by 10^14 (15 digits)
Sum12A = Cell1A + Cell2A + remainder
Sum12 = str(Sum12A) & str(Sum12B)

Alternatively, you could store them in one cell with a string as opposed to an int, and when you wanted to add them, you could convert them into ints within VB, add them, and then convert them to a string again.

link|improve this answer
feedback
up vote 0 down vote

Putting an apostrophy before each entry works only if you are actually keying in the numbers. If you're importing data from a text file or some other process (generally not data you've created yourself) then that answer is moot. I get Excel workbooks from banks all over the country with several sheets that have 14 character account numbers, these sheets are formatted in their entirety as text and the numbers STILL come thru as a scientific notation.

It really blows and it would be nice if Excel had an option to turn that off. Just off.

link|improve this answer
Welcome to SuperUser. When answering questions, you might want to look at how old the question is. – fencepost Apr 23 at 19:08
feedback

Your Answer

 
or
required, but never shown
discard