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​
 
You're using Excel 2013? And did you enter it using Control+Shift+Enter? It's an array formula, but it's not nested more than 4 deep anywhere. Did you actually copy, paste, and CSE enter it? I'm at a bit of a loss, it works fine for me, and I don't get that error.
 
Upvote 0

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.
I tried again and it worked perfectly! THANK YOU! You are a life saver!!
 
Upvote 0
Glad it works for you! Make sure you test it on several cases and check the results. I'm pretty sure it's ok, but you're the one who will be using it. Good luck! :cool:
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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