Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Over 15 Digits in Number (Excel 97)

    We have an Excel form that includes a field for a policy number. This cell is formatted as number, but when we get over 15 digits in that field, it converts any more numbers (like the 16th & 17th digit) to zeros. I found this in the help menu in Excel:

    "15-digit limit Regardless of the number of digits displayed, Excel stores numbers with up to 15 digits of precision. If a number contains more than 15 significant digits, Excel converts the extra digits to zeros (0)."

    This area was a gathering of merged cells, and we found that if we formatted the OTHER cells (the ones we merged into) as text, and the primary cell as number, then it WOULD finally accept over 15 digits, but not if they are all formatted as number. This is really strange. Any of you have any experience with this, and why would Excel limit us to only 15 digits?

    Tracy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Over 15 Digits in Number (Excel 97)

    When you enter a value as a number, then Excel converts the number to a binary floating point number 64 binary bits long. The maximum number of digits that can be stored in that format is 15. If you format the cell as Text, then the value is kept as a string of characters that can be much longer (the exact length depends on what context you are talking in, and what version of Excel). As long as none of the cells that are being merged contain more than 15 digits, then it should not be a problem to merge them into a text string.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Over 15 Digits in Number (Excel 97)

    LC -
    Well, that is what we though at first, as well, and converted the formats to text, however, when we did that, putting in numbers over 15 digits long Excel would convert them to scientific notation
    (123456789101112 +16) instead of leaving them as digits.
    When I try it on my Excel 2000 or Excel 2002 boxes, I don't have these problems. It is just strange

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Over 15 Digits in Number (Excel 97)

    When I said floating point, that is the same as scientific notation. Internally, Excel keeps numbers as a 15 digit decimal and an exponent. However, the number of digits in the mantissa (the fraction) is still limited to 15 digits. The number of digits in your example has 15 digits, which is the maximum. The number can be displayed in scientific notation, or as a decimal number. In your example, if you changed the format to a decimal number it would be displayed as 1234567891011120000000000000000, with no significant digits after the 15th. If you are getting something different than this, would you attach a worksheet with an example.
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Over 15 Digits in Number (Excel 97)

    It is possible to go beyond the 15 digit limit by using Xnumbers, a free (open source) add-in that includes 130 functions at up to 200 significant digits

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Over 15 Digits in Number (Excel 97)-MULTI-Re: Over

    I have encountered similar problems with barcodes. It sounds like you don't need to do any numerical manipulation with the "numbers", so why not treat them all as text strings? This will alleviate the length limitations.

    Even if you do have to treat them as numbers in some context, eg. check for policy numbers between xxx1997zzzz and xxx2002zzzz, then it's easy enough to extract the required substring and convert to integer.

    Alan

  7. #7
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Over 15 Digits in Number (Excel 97)-MULTI-Re: Over

    Thanks Alan & Tony

    We finally got it to work -- it didn't originally work when we re-formatted as a text string. but after monkeying around with it awhile, we finally got it to show the numbers properly. As you said, as far as I know they are not going to need to sort or search by this field in this particular case, but I wanted the information for when it comes up again.
    Tracy

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Over 15 Digits in Number (Excel 97)

    When you enter the Policy number just start by typing the apostrophe character first e.g. '123456789123456789

    regards

    zeddy

Posting Permissions

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