# Thread: Sixteen digit number formatting problem

1. ## Sixteen digit number formatting problem

Using Excel 2010 and trying to enter a sixteen digit number into a cell.
For example entering 1234567812345678 the number entered is an exponential.

Formatting the cell to Number with zero after decimal point the results is 1234567812345670.
The last digit changes from 8 to 0. Why? Is it because of the limit in Excel in number digits?

I can format as Text, but then it is no longer a number.

Any suggestions,

Thanks

2. Why does it need to be a number? Are you planning to do mathematical operations on it?

3. You can still do calculations on it by changing it back to a value, performing the calculation, and then converting it back to text

=TEXT(VALUE(C1)*2,"#")

ValToText.png

In the calculation, however, the trailing 8 gets converted to a zero using the Value() function.

HTH,
Maud

4. So why bother storing it as text, since you lose the precision anyway?

5. for visual purposes only.

6. Actually it is a credit card number and therefore need not be calculated. My conclusion was to format the cell as Text, then right hand justify to appear to be a number. The small triangle in the upper left hand side of the cell tells me it is a Text.
I thought there was a limit to the numbers in a cell, but could not remember the details.

Thanks to one and all for the quick reply.

7. Richard,

If formatted as text there is only the text length limit to contend with. Which for Excel 2010 is 32767 (which seems odd but that's what is says).
fmtastext.JPG
HTH

8. Hi RG

..that's the biggest credit card number I've ever seen. Is it Super-Platinum???

zeddy

9. Zeddy,

I make so much here in the Lounge that I've opened my own bank!
pig.gif
ROTFLOL.gif

10. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

zeddy (2015-03-25)

11. Originally Posted by RetiredGeek
Richard,

If formatted as text there is only the text length limit to contend with. Which for Excel 2010 is 32767 (which seems odd but that's what is says).
fmtastext.JPG
HTH
RG:

32767 = 2^15 - 1. (2 to the 15th power).

Also, we find these other interesting limits:

Number precision is 15 digits.
Iterations is 32767.
Linked cell content length from closed workbooks is 32767.
Length of the MDX name for a PivotTable item is 32767.
Length for a relational PivotTable string is 32767.
Days that change history is maintained is 32767.
Cells that can be highlighted in a shared workbook is 32767.

Some variant of 15 seems to be the limit they have decided on for many of these limits.

Jim

12. Jim,

The reason I said it seems odd is that i remember 32,535 or some such as a common limit. But, then I'm getting old and the grey matter is hardening up a bit.

13. In a previous lifetime, when I was a programmer, I remember that they were always using 2 to some power, probably because at the most basic level, everything in the computer is in binary (base 2).

You likely know that, but not everyone does.

I am curious as to why they decided on 15. I guess they had to pick a number, and 15 was as good as any.

14. Perhaps left over from 16 bit programming where either the first or the last bit was used as a pointer.

15. You need more than 16 bits to represent a 15 digit decimal number!

I think that 15 was the age of the programmer who wrote Excel's Name "Manager".

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•