Add more than 20 digits in Excel without an exponential sign

Question

I want to add more than 20 digits in an Excel cell. The current format of the cell is general, and 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 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.

Solution 1

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.

Solution 2

I know absolutely nothing about Excel, but I believe you can write Visual Basic code for it. 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 integers within Visual Basic, add them, and then convert them to a string again.

Comments

[Damien_The_Unbeliever]

If you want to keep it as text, just put an ' before it, e.g. '1234567890123456789

[fencepost]

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]

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.

[Ravindra Bawane]

But this assumes all of your values are a certain number of digits (18 in your example) and does not scale to any other size number, plus it does not solve the problem of allowing arithmetic operations on the values.