Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Richard Spring

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Why does it need to be a number? Are you planning to do mathematical operations on it?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    So why bother storing it as text, since you lose the precision anyway?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    for visual purposes only.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.
    Richard Spring

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi RG

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

    zeddy

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    I make so much here in the Lounge that I've opened my own bank!
    pig.gif
    ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    zeddy (2015-03-25)

  12. #11
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by RetiredGeek View Post
    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
    Last edited by mrjimphelps; 2015-03-24 at 17:47. Reason: additional info added

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #13
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    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.

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Perhaps left over from 16 bit programming where either the first or the last bit was used as a pointer.

  16. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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 LastLast

Posting Permissions

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