Over 15 Digits in Number (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

regards

zeddy

Reply | Quote