Check Digit Mod 10 with weights 7-5-3-2

F

fastmr291

New Member
Joined
Jun 18, 2014
Messages
4
I need to create a check digit for a financial institution's scanline and am not quite sure how to make that happen using the 7532 weights. The appeal code and package code should be static so I will just assign the appropriate numbers for the letters and will build one string containing all of the numbers for the scanline without spaces. That is unless I should do it a different way. I found some VB code but I am unsure how to use it so if we can get everything to work in Excel or Access that would be wonderful. Thank you for your assistance and I await your responses.

~Chris


The instructions I received are as follows...

Total # of characters: 34
Font: OCR-A


The scan line is 34 characters long including all spaces. It is as follows:

POSITIONS START-END LENGTH DESCRIPTION
1-10 10 ID#1
11 1 Space
12-17 6 Fund2
18 1 Space
19-24 6 Appeal3
25 1 Space
26-32 7 Package4
33 1 Space
34 1 Check Digit Verifier5


1 – ID# - all ID#’s from the client should be 10 characters

2 – Fund - always 451000

3 – Appeal – DM# - 0 fill after DM if necessary (ie DM0213)

4 – Package – Keycode (ie 405R914)

5 – Check digit (for whole scan line)



Alpha Conversion

[TABLE="width: 285, align: left"]
<tbody>[TR]
[TD]Numeric Value
[/TD]
[TD="colspan: 3"]Alpha Value
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]J
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[TD]K
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]C
[/TD]
[TD]L
[/TD]
[TD]U
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]D
[/TD]
[TD]M
[/TD]
[TD]V
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]E
[/TD]
[TD]N
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]F
[/TD]
[TD]O
[/TD]
[TD]X
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]G
[/TD]
[TD]P
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]H
[/TD]
[TD]Q
[/TD]
[TD]Z
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]I
[/TD]
[TD]R
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 285"]
<tbody>[TR]
[TD]
[/TD]
[TD="colspan: 3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]




[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
Check Digit (CD) Routine – client required calculation


1. Identify the series of numbers
Ex. 1000108030 451000 DM0213 405R914

2. Convert any alpha characters to a number based on the conversion chart above

[TABLE="width: 718"]
<tbody>[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]D
[/TD]
[TD]M
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]R
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

3. Assign weights from left to right 7,5,3,2

[TABLE="width: 727"]
<tbody>[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]


4. Multiply each number by its assigned weight

[TABLE="width: 814"]
<tbody>[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]24
[/TD]
[TD]0
[/TD]
[TD]21
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]10
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]28
[/TD]
[TD]20
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]0
[/TD]
[TD]35
[/TD]
[TD]45
[/TD]
[TD]27
[/TD]
[TD]2
[/TD]
[TD]28
[/TD]
[/TR]
</tbody>[/TABLE]


5. Sum the digits of the results (1st sum the numbers with 2 digits and then sum the entire line, ie: 24 = 2+6=8)

[TABLE="width: 775"]
<tbody>[TR]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]24
[/TD]
[TD]0
[/TD]
[TD]21
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]10
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]28
[/TD]
[TD]20
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]0
[/TD]
[TD]35
[/TD]
[TD]45
[/TD]
[TD]27
[/TD]
[TD]2
[/TD]
[TD]28
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]2+4=6
[/TD]
[TD]0
[/TD]
[TD]2+1=3
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]1+2=3
[/TD]
[TD]1+0=1
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]2+8=10
[/TD]
[TD]2+0=2
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[TD]1+5=6
[/TD]
[TD]
[/TD]
[TD]1+2=3
[/TD]
[TD]0
[/TD]
[TD]3+5=8
[/TD]
[TD]4+5=9
[/TD]
[TD]2+7=9
[/TD]
[TD]2
[/TD]
[TD]2+8=10
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]14
[/TD]
[TD]14
[/TD]
[TD]20
[/TD]
[TD]20
[/TD]
[TD]23
[/TD]
[TD]23
[/TD]
[TD][/TD]
[TD]26
[/TD]
[TD]27
[/TD]
[TD]34
[/TD]
[TD]34
[/TD]
[TD]34
[/TD]
[TD]34
[/TD]
[TD][/TD]
[TD]44
[/TD]
[TD]46
[/TD]
[TD]46
[/TD]
[TD]50
[/TD]
[TD]57
[/TD]
[TD]63
[/TD]
[TD][/TD]
[TD]66
[/TD]
[TD]66
[/TD]
[TD]74
[/TD]
[TD]83
[/TD]
[TD]92
[/TD]
[TD]94
[/TD]
[TD]104
[/TD]
[/TR]
</tbody>[/TABLE]


6. Divide the sum by the modulus 10 to determine the remainder
Ex: 104/10=10.4 – the remainder is 4

7. Subtract the remainder* from the Modulus 10 to determine the check digit
* If the remainder is 0, then the check digit equals 0
Ex: 10-4=6 – the check digit is 6
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
You wish to create the CheckDigit when given a ScanLine or you want to validate the CheckDigit?
 
Upvote 0
Either way this will generate a Check Digit:

Code: 
Function CheckDigit(ScanLine As String)


Dim InArray(1 To 29) As Long
Dim AlphaConvArray As Variant
Dim WeightArray As Variant
Dim i As Long, j As Long
Dim ScanStr As String
Dim ScanLineSum As Long


If Len(ScanLine) <> 32 Then GoTo ExitUDF
If Not Len(ScanLine) - Len(Replace(ScanLine, " ", "")) = 3 Then GoTo ExitUDF
If Not IsNumeric(Left(ScanLine, 10)) Then GoTo ExitUDF
If Not Mid(ScanLine, 12, 6) = "451000" Then GoTo ExitUDF


ScanStr = Replace(ScanLine, " ", "")
AlphaConvArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
WeightArray = Array(7, 5, 3, 2)


For i = 1 To 29
    If IsNumeric(Mid(ScanStr, i, 1)) Then
        InArray(i) = CLng(Mid(ScanStr, i, 1)) * WeightArray((i - 1) Mod (UBound(WeightArray) + 1))
    Else
        InArray(i) = AlphaConvArray((Asc(LCase(Mid(ScanStr, i, 1))) - 98) Mod (UBound(AlphaConvArray) + 1) + 1) * WeightArray((i - 1) Mod (UBound(WeightArray) + 1))
    End If


    For j = 1 To Len(Trim(InArray(i)))
        ScanLineSum = ScanLineSum + CLng(Mid(InArray(i), j, 1))
    Next j
Next i


CheckDigit = 10 - ScanLineSum Mod 10
Exit Function


ExitUDF:
CheckDigit = "ScanLine not valid, cannot generate CheckDigit"
Exit Function


End Function

Please note that I didn't understand everything that you requested so it will probably need amending.
 
Upvote 0
Hi,</SPAN></SPAN>

Alternative, formula-based solution:

Array formula**:

=10-MOD(SUM(0+MID(TEXT(IF(1-ISNUMBER(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),1+MOD(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-65,9),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MID(7532,1+MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,4),1),"00"),{1,2},1)),10)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
 
Upvote 0
And an alternate (shorter) UDF...
Code: 
Function CheckDigit(ScanLine As String) As Long
  Dim X As Long, Digits As Variant
  On Error GoTo SomethingIsWrongWithScanLine
  Digits = Split(StrConv(UCase(Replace(ScanLine, " ", "")), vbUnicode) & "0", Chr(0))
  For X = 0 To UBound(Digits) - 1
    If Digits(X) Like "[A-Z]" Then Digits(X) = ((Asc(Digits(X)) - 65) Mod 9) + 1
    Digits(X) = Digits(X) * Mid("7532", 1 + (X Mod 4), 1)
    If Digits(X) > 9 Then Digits(X) = CLng(Left(Digits(X), 1)) + CLng(Right(Digits(X), 1))
  Next
  CheckDigit = 10 - (Evaluate(Join(Digits, "+")) Mod 10)
  Exit Function
SomethingIsWrongWithScanLine:
  CheckDigit = -99
End Function
Note: If an invalid scanline is passed into the function, then -99 is returned to signal an error was encountered.
 
Upvote 0
Rick Rothstein said:
And an alternate (shorter) UDF...
Code: 
Function CheckDigit(ScanLine As String) As Long
  Dim X As Long, Digits As Variant
  On Error GoTo SomethingIsWrongWithScanLine
  Digits = Split(StrConv(UCase(Replace(ScanLine, " ", "")), vbUnicode) & "0", Chr(0))
  For X = 0 To UBound(Digits) - 1
    If Digits(X) Like "[A-Z]" Then Digits(X) = ((Asc(Digits(X)) - 65) Mod 9) + 1
    Digits(X) = Digits(X) * Mid("7532", 1 + (X Mod 4), 1)
    If Digits(X) > 9 Then Digits(X) = CLng(Left(Digits(X), 1)) + CLng(Right(Digits(X), 1))
  Next
  CheckDigit = 10 - (Evaluate(Join(Digits, "+")) Mod 10)
  Exit Function
SomethingIsWrongWithScanLine:
  CheckDigit = -99
End Function
Note: If an invalid scanline is passed into the function, then -99 is returned to signal an error was encountered.
Click to expand...
Slightly modified to reduce it by one line of code...
Code: 
Function CheckDigit(ScanLine As String) As Long
  Dim X As Long, Digits As Variant
  On Error GoTo SomethingIsWrongWithScanLine
  Digits = Split(StrConv(UCase(Replace(ScanLine, " ", "")), vbUnicode) & "0", Chr(0))
  For X = 0 To UBound(Digits) - 1
    If Digits(X) Like "[A-Z]" Then Digits(X) = ((Asc(Digits(X)) - 65) Mod 9) + 1
    Digits(X) = Evaluate(Format(Digits(X) * Mid("7532", 1 + (X Mod 4), 1), "0+0"))
  Next
  CheckDigit = 10 - (Evaluate(Join(Digits, "+")) Mod 10)
  Exit Function
SomethingIsWrongWithScanLine:
  CheckDigit = -99
End Function
 
Upvote 0
Thank you for your responses. I will test these out this afternoon and will let you know how everything turned out.
 
Upvote 0
Worked wonderfully!!!! Thank you very much!!



XOR LX said:
Hi,

Alternative, formula-based solution:

Array formula**:

=10-MOD(SUM(0+MID(TEXT(IF(1-ISNUMBER(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),1+MOD(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-65,9),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MID(7532,1+MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,4),1),"00"),{1,2},1)),10)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Click to expand...
 
Upvote 0
I stole this formula and it work perfectly...except that in my case, the check# cannot be over 9. In other words, if the remainder is 10, the ck# is 0. Can you help me with that?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
55
daveyc18
D
A
  • Question Question
Need a formula to calculate a MOD10 check digit for a string of 26 numbers.
Replies
2
Views
1K
Georgiboy
Georgiboy
S
  • Solved
VBA Code Help
Replies
4
Views
492
Peter_SSs
Peter_SSs
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
207
JV0710
J
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
254
etaf
etaf

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

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
Back
Top