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.

Peter Mortensen
  • 12,326

5 Answers5

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.

Lance Roberts
  • 8,837
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.

Peter Mortensen
  • 12,326
Michael
  • 165
  • 9
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)
music2myear
  • 49,799
Margarida Brito
  • 1
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.

Ashwin Balaji Kuppuraj
  • 1
-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.

user269554
  • 1