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

JacqueB

New Member
Joined
Sep 3, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I need to create a check digit for a financial institution’s scanline. The scanline includes alpha characters that need converted to numbers according the scale below.
I need a formula I can use in Excel 2013. My scanline data is in a single column in Excel and I need to apply the formula in Excel to generate the check digit.
Thank you in advance for your assistance

The details are as follows:

The scan line is 23 characters long – no spaces. It is as follows:

POSITIONS START – END LENGTH DESCRIPTION:

1-4 Last name alpha characters (4 characters)

5-12 Unit number (8 characters)

13-22 Amount owed (10 characters)

23 Check Digit

The check digit is being generated as “Modulus 10, sum of digits, with weights of 7,5,3,2.” with the letters replaced by numbers as in the image below.

NUMERIC VALUE​
ALPHA VALUE​
ALPHA VALUE​
ALPHA VALUE​
1​
A​
J​
S​
2​
B​
K​
T​
3​
C​
L​
U​
4​
D​
M​
V​
5​
E​
N​
W​
6​
F​
O​
X​
7​
G​
P​
Y​
8​
H​
Q​
Z​
9​
I​
R​





The resulting sequence of 22 numbers is then run through an algorithm to come up with the check digit. The whole sequence for someone with the last name Munoz, in unit V203-3, who owes $556.15 is below.

Last Name​
Unit Number​
Amount Owed​
Original Sequence
M U N O​
0 V 2 0 3 X 0 3​
0 0 0 0 0 5 5 6 1 5​
Converted to Numbers
4 3 5 6​
0 4 2 0 3 6 0 3​
0 0 0 0 0 5 5 6 1 5​
Multiply each number by:
7 5 3 2​
7 5 3 2 7 5 3 2​
7 5 3 2 7 5 3 2 7 5​
Result for each number
28 15 15 12​
0 20 6 0 21 30 0 6​
0 0 0 0 0 25 15 12 7 25​
Add digits
10 6 6 3​
0 2 6 0 3 3 0 6​
0 0 0 0 0 7 6 3 7 7​
Sum total
75​
Subtract last digit from 10
5​
Final OCR Line
M U N O
0 V 2 0 3 X 0 3​
0 0 0 0 0 5 5 6 1 5 5​
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807
Welcome to MrExcel!

A question. When you convert the letters to numbers, how do you do that? It appears to be using the EBCDIC sequence, true?
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
This post has confused me so much.

Got an email to say JacqueB had replied to a 6 year old thread I was apart of. But could see no such reply in the thread:


And then see this thread on the forum:


...... How did I get an email??!>!

Does the forum only check thread description??
 

JacqueB

New Member
Joined
Sep 3, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I am obviously not a programmer so I do not know what EBCDIC sequence is. I included the table to covert letters to numbers in my orginal post. It is 1 = AJS, 2 = BKT, etc.
When you 'add digits' to 'sum total', you add the digits across the 3 columns 10,6,6,3,0,2,6,0,3,3,0,6,0,0,0,0,0,7,6,3,7,7 to get 75.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807

ADVERTISEMENT

Sorry, I didn't read your post completely, I see it explains how to convert the letters. I'll take a stab at it, but you might want to look at the first link in post 3, it has formula and VBA options that might already work for you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,536
Office Version
  1. 365
Platform
  1. Windows
Got an email to say JacqueB had replied to a 6 year old thread I was apart of. But could see no such reply in the thread:
JacqueB did post to the old thread, but it was removed as a duplicate, because this thread had already been started.
 

JacqueB

New Member
Joined
Sep 3, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Sorry, I didn't read your post completely, I see it explains how to convert the letters. I'll take a stab at it, but you might want to look at the first link in post 3, it has formula and VBA options that might already work for you.
I did try that formula, but it did not work for me. Thank you in advance for taking a stab at it!
 

JacqueB

New Member
Joined
Sep 3, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
JacqueB did post to the old thread, but it was removed as a duplicate, because this thread had already been started.
I am sorry if I caused confusion! I am new to posting on Forums. I am desperate for help and I was unsure where to post. Sorry....
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807
OK, try this:

Book1
AB
2MUNO0V203X0300000556155
Sheet7
Cell Formulas
RangeFormula
B2B2=MOD(10-MOD(SUM(MOD((IFERROR(MID(A2,ROW(INDIRECT("1:22")),1)+0,MOD(CODE(UPPER(MID(A2,ROW(INDIRECT("1:22")),1)))-2,9)+1)*{7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5}),10))+SUM(INT((IFERROR(MID(A2,ROW(INDIRECT("1:22")),1)+0,MOD(CODE(UPPER(MID(A2,ROW(INDIRECT("1:22")),1)))-2,9)+1)*{7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5})/10)),10),10)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

JacqueB

New Member
Joined
Sep 3, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
OK, try this:

Book1
AB
2MUNO0V203X0300000556155
Sheet7
Cell Formulas
RangeFormula
B2B2=MOD(10-MOD(SUM(MOD((IFERROR(MID(A2,ROW(INDIRECT("1:22")),1)+0,MOD(CODE(UPPER(MID(A2,ROW(INDIRECT("1:22")),1)))-2,9)+1)*{7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5}),10))+SUM(INT((IFERROR(MID(A2,ROW(INDIRECT("1:22")),1)+0,MOD(CODE(UPPER(MID(A2,ROW(INDIRECT("1:22")),1)))-2,9)+1)*{7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5;3;2;7;5})/10)),10),10)
Press CTRL+SHIFT+ENTER to enter array formulas.

I received an error message "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,502
Messages
5,636,694
Members
416,935
Latest member
Atulcp

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