1

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.

3
  • 2
    If you want to keep it as text, just put an ' before it, e.g. '1234567890123456789 Feb 11, 2011 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, 2012 at 19:06
  • 1
    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, 2012 at 19:13

5 Answers 5

5

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.

1

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.

0

The issue here starts from pasting it into Excel itself. So Text to column option will not help here.

Better change the cell format to "Text" and then you can copy the ">15" digit numbers into excel cell.

0

Because Excel only works with numeric values up to 15 digits you have to separate the number and then concatenate:

Example:

  • A2=894450360114541404
  • B2=10000
  • C2=894450360114551404

To add A2+B2 and have the result in C2 which has the formula:

=CONCATENATE(LEFT(A2,10),NUMBERVALUE(RIGHT(A2,8))+B2)
1
  • 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. Jan 9, 2017 at 18:11
-1

Example: 12345678901234567890 Excel converts it to 1.23457E+19 in general format. Excel converts it to 12345678901234500000 in number & accounting format.

You can use the text to column option. Use the fixed delimiter and split the number in half (if really long then into smaller chuncks).

Once you have the two columns, in the cell next to it use =concantenate(A2,A3). This will give you the complete number.