Results 1 to 8 of 8

20020327, 22:19 #1
 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:
"15digit 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

20020328, 01:48 #2
 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

20020328, 13:53 #3
 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

20020328, 15:42 #4
 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

20020330, 09:20 #5
 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) addin that includes 130 functions at up to 200 significant digits

20020405, 03:14 #6
 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)MULTIRe: 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

20020405, 14:13 #7
 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)MULTIRe: Over
Thanks Alan & Tony
We finally got it to work  it didn't originally work when we reformatted 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

20020405, 14:52 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 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