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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,531
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top