Mod 10 check digit from 38 byte number

johnnysuperhero

New Member
Joined
Sep 15, 2006
Messages
1
I saw the forum on the Mod 10 check digit and attempted to alter the formula to fit my needs, but yeah...I'm not the brightest bulb on the tree. Plus my numbers are 38 bytes long, which can cause some problems.

For this Mod 10 check digit, for those not familiar, you follow a multiplication sequence beginning with the last digit. The last is multiplied by two, the next is by one, the next is by two, then one, then two, and so on until you're at the first digit. (2x1x2x1x2x1x2)

When you come across a digit that will provide a 2 digit answer (ie a 9 would give an answer of 18) you need to separate the two digits and add them together. (2x9 = 18 = 1+8 = 9)

You then need to add up all of your new digits, divide the total by 10, and subtract the remainder from 10 to get the coveted check digit that I'm after. (67 / 10 = 6 r7 = 10 - 7 = check digit of 3)

I appreciate any help on this one guys. Thanks!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi
Welcome to the board

From your description I assumed that for example for 3596 you have

6 * 2 = 12 = 1+2 = 3
9 * 1 = 9
5 * 2 = 10 = 1+0 = 1
3 * 1 = 3

Sum = 3 + 9 + 1 + 3 = 16

Mod 10 Check Digit = 20 - 16 = 4

If this is not the correct algorithm please explain it a little more and, VERY IMPORTANT, always post for any question some examples with the input and the expected output so that we can test the final solution.

If this is the correct algorithm, write in B2

Code:
=MOD(10-MOD(SUM(IF(MOD(ROW(INDIRECT("1:"&LEN(A2))),2),2*MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)-9*(--MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)>=5),--MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1))),10),10)
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down

Please check it.

I did some examples and it seems OK (for this algorithm).

Hope this helps
PGC
Book1
ABCD
1CodeMod10CD
2012345678901234567890123456789012345675
335998
435964
530056000080008
65402150000001061
749927398716
8835173
9
10
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,634
Members
410,804
Latest member
bluepinky
Top