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, 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.

share|improve this question

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

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

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 '12 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 '12 at 19:13

4 Answers 4

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

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