Can a MOD-10 Calculation Be Done in Excel?

[kmeunier]

I need to add a check digit to the end of a numeric short keyline using the MOD-10 calculation routine. Can this be done in Excel?

Thank you so much!

Kathy

 

[pgc01]

Hi Kathy

You can implement the MOD-10 algorithm in vba. There are examples of code in VB or other languages on the web and you can translate one of them into vba.

You can also use a formula.

Format A1 as text and exter your code without the check digit.

To get the check digit, in B1:

=MOD(1000-SUM(IF(MOD(ROW(INDIRECT("1:"&LEN(A1&0))),2)=1,--MID(A1&0,1+LEN(A1&0)-ROW(INDIRECT("1:"&LEN(A1&0))),1),MOD(2*MID(A1&0,1+LEN(A1&0)-ROW(INDIRECT("1:"&LEN(A1&0))),1),10)+INT(2*MID(A1&0,1+LEN(A1&0)-ROW(INDIRECT("1:"&LEN(A1&0))),1)/10))),10)

This in an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Examples:
12345, check digit:5, whole code 123455
12345678998706543, check digit:7, whole code 123456789987065437

Hope this helps
PGC

 

[kmeunier]

Formula for a MOD-10 check digit for AlphaNumeric Keyline

The formula for the numeric keyline worked flawlessly - Thank you so much! I've just learned that we'll have some alphanumeric keylines that will require a MOD-10 check digit as well. I'm really not sure how to proceed with that. For example: FST1234567 requires a MOD-10 check digit. Is it possible to have a formula assign the check-digit?

Thank you,
~Kathy

 

[pgc01]

Hi Kathy

I'm glad I could help. It's always gratifying when a formula is really used.

About the mod10 for the alphanumeric. I know it's used but the truth is that I've never looked into it.
This means that I cannot help you now. I'll try to find information about it and if no one else comes up with a solution I'm sure I can give you a solution in 1 or 2 days. I'm also interested in the solution.

By the way, does it have to be a formula or can it be an UDF? Are there security issues in your company that prevent you from using vba?

A last point. Since you work with it, maybe you know of a site that has good descriptions of the specs and an algorithm for the mod10 for the alphanumeric. If that is the case please post its address.

Kind regards
PGC

 

[kmeunier]

MOD-10 Check Digit for Alphanumeric Keyline

Hi PC,

I'm just starting to use the MOD-10 check digit for "numeric" keylines based on ACS (address change service) requirements by the USPS. Their specs state the following:

USPS Standards for Check Digit Computation

USPS Standards for Check Digit Computation
Note: The value of the letter "N" in an odd position, when weighted, becomes 14 x 2 = 28. The digits 2 and 8 are added, yielding 10. This sum is then further rendered as 1 + 0, not the integer 10.

ACS participants using the keyline option must use the following algorithm to compute the check digit:

MOD 10

1. Consider the keyline as an ASCII string of 3 to 15 characters. Remove any spaces before beginning calculation.

2. Convert the remaining characters to pure numeric values by zeroing out all but the lower four bits. This yields the following values for each alpha character (numeric characters retain their value):

A = 1 E = 5 I = 9 M = 13 Q = 1 U = 5 Y = 9
B = 2 F = 6 J = 10 N = 14 R = 2 V = 6 Z = 10
C = 3 G = 7 K = 11 O = 15 S = 3 W = 7 blank
D = 4 H = 8 L = 12 P = 0 T = 4 X = 8 / = 15

3. Multiply the value at each odd-numbered position (first position, third position, fifth position, etc.) by a weighting factor of 2.

4. Add the digits (not the actual value) in all positions. For example, if the second position of the keyline is "L," whose value is 12, add the digits 1 + 2 (not the value 12) to the sum.

Note: The value of the letter "N" in an odd position, when weighted, becomes 14 x 2 = 28. The digits 2 and 8 are added, yielding 10. This sum is then further rendered as 1 + 0, not the integer 10.

5. Subtract the right-most digit of the sum from 10, yielding the check digit.

Note: If the right-most digit of the sum is 0, the check digit will be 0

Example 1. Computation of Check Digit for Keyline JLSTMS6796

Character J L S T M S 6 7 9 6
Position 1(O) 2(E) 3(O) 4(E) 5(O) 6(E) 7(O) 8(E) 9(O) 10(E)
Value 10 12 3 4 13 3 6 7 9 6
Weighted Value 20 12 6 4 26 3 12 7 18 6
Sum 2+0 +1+2 +6 +4 +2+6 +3 +1+2 +7 +1+8 +6 = 51

Right-most Digit of Sum: 1

Check Digit: 10-1=9

Complete Keyline: JLSTMS67969

Example 2. Computation of Check Digit for Keyline TMS1112/62

Character T M S 1 1 1 2 / 6 2
Position 1(O) 2(E) 3(O) 4(E) 5(O) 6(E) 7(O) 8(E) 9(O) 10(E)
Value 4 13 3 1 1 1 2 15 6 2
Weighted Value 8 13 6 1 2 1 4 15 12 2
Sum 8 +1+3 +6 +1 +2 +1 +4 +1+5 +1+2 +2 = 37

Right-most Digit of Sum: 7

Check Digit: 10-7=3

Complete Keyline: TMS1112/623

Example 3. Computation of Check Digit for Keyline 218XN91LMS

Character 2 1 8 X N 9 1 L M S
Position 1(O) 2(E) 3(O) 4(E) 5(O) 6(E) 7(O) 8(E) 9(O) 10(E)
Value 2 1 8 8 14 9 1 12 13 3
Weighted Value 4 1 16 8 28 9 2 12 26 3
Sum 4 +1 +1+6 +8 +1+0 +9 +2 +1+2 +2+6 +3 = 46

Right-most Digit of Sum: 6

Check Digit: 10-6 = 4

Complete Keyline: 218XN91LMS4

The following are additional examples of valid keylines that you may use for testing your algorithm. In each case, the check digit is the last character.

ABC999NN//32 OTUBIKALAM8
ANIT0281 SEIRAMT4
0012////8 14024981490
811NIS0001119 AEIOUW0
12345678907 DCS14020
09876543213 blank

Note: Although optional for COA information, keylines are required if the mailer wants to receive electronic ACS nixie notifications. Also, each keyline must end with a check digit correctly calculated using the Postal Service standard for check digit computation.

I'm not as advanced in Excel as you are (not even close), nor have I used VBA (never, ever). Although I'm a firm believer that nothing is impossible and would be more than willing to attempt it. My business has no policy that I'm aware of that prevents it either.

Thank you,
~Kathy

 

[pgc01]

Thanks for the specs.
I'll go offline now.

I'll get back to you when I have news.
PGC

 

[pgc01]

Hi Kathy

I read the specs. The logic for the calculation of the Check Digit for an alphanumeric keyline is almost the same as the one for a numeric keyline. There is one main difference, however, that makes them incompatible. You analyse the numeric keyline from right to left, whereas in the case of the alphanumeric you go left to right. I wonder why they chose to make them incompatible...

I wrote this UDF to implement it.

This is what it does

- Erases the spaces from the keyline (Specs point 1)
- Validates number of characters (3 to 15) (Specs point 1)
- Validates the characters (digits, letters and slash) (Specs point 2)
- Calculates the Check Digit

To install it:
- Right click on your worksheet's tab and choose View Code. This will take you to the VB Editor
- Insert>Module. This will insert a new module named Module1 (unless you already have other modules. In that case the name would be Module2 or 3...)
- Paste the code I post
- File>Close and Return to Microsoft Excel (or ALT-Q)

Now in the worksheet you use this UDF as any other formula

If the keyline is in A1, to get the Check digit, in B1
=Mod10ChkDig(A1)

If the keyline is in A1, to get the final keyline that includes the Check digit, in B1
=A1 & Mod10ChkDig(A1)

I tested the code with all the examples in the specs. Please test it further.

Hope it's clear
PGC

Function Mod10ChkDig(ByVal sKeyline As String) As String
Dim iSum As Integer, iCharVal As Integer, i As Integer

' Remove spaces
sKeyline = Replace(sKeyline, " ", "")

' Must have between 3 and 15 characters
If Len(sKeyline)< 3 Or Len(sKeyline) > 15 Then
    Mod10ChkDig = "Invalid number of characters in Keyline"
    Exit Function
End If

' Characters allowed: digits, letters and slash
If sKeyline Like "*[!0-9a-zA-Z/]*" Then
    Mod10ChkDig = "Invalid characters in Keyline"
    Exit Function
End If

' Calculates check digit
For i = 1 To Len(sKeyline)
    iCharVal = (1 + i Mod 2) * (Asc(Mid(sKeyline, i, 1)) And 15)
    iCharVal = (iCharVal \ 10) + (iCharVal Mod 10)
    iSum = iSum + iCharVal + IIf(iCharVal = 10, -9, 0)
Next
Mod10ChkDig = (10 - (iSum Mod 10)) Mod 10
End Function

Mod10.xls
	A	B	C	D	E
1	Keylinew/o CheckDigit	CheckDigit Specs	CheckDigit Calculated	Keylinewith CheckDigit	Test
2	JLSTMS6796	9	9	JLSTMS67969	OK
3	TMS1112/62	3	3	TMS1112/623	OK
4	218XN91LMS	4	4	218XN91LMS4	OK
5	ABC999NN//3	2	2	ABC999NN//32	OK
6	OTUBIKALAM	8	8	OTUBIKALAM8	OK
7	ANIT028	1	1	ANIT0281	OK
8	SEIRAMT	4	4	SEIRAMT4	OK
9	0012////	8	8	0012////8	OK
10	1402498149	0	0	14024981490	OK
11	811NIS000111	9	9	811NIS0001119	OK
12	AEIOUW	0	0	AEIOUW0	OK
13	1234567890	7	7	12345678907	OK
14	DSC1402	0	0	DSC14020	OK
15	0987654321	3	3	09876543213	OK
Sheet2

 

[kmeunier]

Hi PGC!
I've tested this and I can't find any problems. It's awesome -- much like you! Thank you sooooo much for your help. This has allowed us to move forward with our ACS project sooner rather than later.

Best regards,
~K

 

[pgc01]

I'm glad I was able to help.

Cheers
PGC

 

[IDAL]

I'm glad I was able to help.

Cheers
PGC

Thanks!