15 digits limit, need to show more then that in a cell !

[Tojje]

Dear all,

a problem I have never experienced before occured today.

If I do calculations in a cell and the result of that becomes more then 15 digits. That is a problem for me when excel cant handle more then 15 digits.
Is there a way for me to come around that ?
(every digit needs to be present in the cell when other calculations are based on this figure )

Thanks for your help !
Many thanks
/JE

[Mark W.]

What's the calculation?

[bergy]

I found this in the help files. Doesn't sound good...

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

[Tojje]

I am involved in a barcode project.
A partnumber may look like this H7491490201J2 and to calculate the Check Character in a barCode you need to add each figure in the partnumber (H+7+4+9+1+4+9+0+2+0+1+J+2) Each letter needs to be translated into a figure i.e H=17 etc.

If you are to add each figure in that partnumber you need to translate H with 881 which becomes 17 when added together. If you have a partnumber including more then 2 letters it will end up to more then 15 digits and thats were I am today.

/JE

[Mark W.]

Are you adding or concatenating? Because if you're adding I don't see how you're exceeding 15 digits. What is the check value for H7491490201J2? What are the subsequent calculations that you're performing on the check value?

[ This Message was edited by: Mark W. on 2002-05-21 08:25 ]

[Tojje]

H7491490201J2 is a 13 digit number, but to make calculations you need to convert the H and J into figures.
H respresents 17
J represents 19

Due to I need to add each figure and then get the sum of that I cant just add 17 due to the actual sum of that is 8 and NOT 17. so the solution for that is to put, for instance, 881 which reslut in the sum of 17 etc.

It ends up being more then 15 digits in a cell where the original partnumber contains letters.

/JE

[Mark W.]

What is the check value for H7491490201J2? What are the subsequent calculations that you're performing on the check value?

[Tojje]

Ok !

Everything is based on the sum of adding each figure in a cell. ( After converting letters into digits )

The complete ProductNumber look like this from start:
+H7491490201J2

This results after convertion in the below figure:
9999588174914902019912 (22 digit number which are causing all problems due to the 15 figures limit)

The CheckSum from this is 116

From that, using Modulus 43 calculations, I get a remainder of 30

The figure 30 is then converted into a Modulus 43 Chech Character which in this example becomes the letter U

This letter is then added to the original partnumber,at the end.(+H7491490201J2U)

NB! The partnumber used when order a such product, which is typed into our ordering system is: H7491490201J2
I need to calculate the CheckCharacter for thousnads of products to build up an easy BarCode convertion table so we in the end can implement such an ordering system for our customers. We do not have a list today over how each barcode reads copared to the actual partnumber put into our ordering system. This information is not optainable for us due to centraslization of our warhouse.

Hope that clear a few thing out.
The main thing though is how to come around the 15 digit block within Excel

Regards
/JE

[Mark W.]

Okay, store your 22-digit value as text, "9999588174914902019912" (in cell A1) . Use the array formula...

{=SUM(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)}

... to calculate the CheckSum, 116, and go from there.

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


[ This Message was edited by: Mark W. on 2002-05-21 10:11 ]

[Tojje]

I appreciate your intentions with helping me out but I can not format all cells into "Text" due to it ends up with something like : 8,56497E+14 and that is not applicable when applying the formula. The formula is one I already use for calculating the CheckSum but with more then 15 digits its not possible.

When converting the letters in a productnumber I use the "Replace" function due to the ampunt of partnumbers to convert. I then end up with the result looking similar to the above mention figure( when formatted all cells into text fields)

I am afraid I need another solution if there is one ?

Regards
JE