Mod 43 Check Digit calculator

[charltondav]

I am trying to calculate the check sum character for a set of data. I found the following formula that seems to work for most characters/values, but it does not work for % (42). Any suggestions?

=MID("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%",
MOD(SUMPRODUCT(SEARCH(MID(A1,ROW($1:$99),1),
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%") )-98,43),1)

[shg]

What should the checksum character be?

[charltondav]

The character for 42 should be %.

0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
A 10
B 11
C 12
D 13
E 14
f 15
G 16
h 17
i 18
j 19
k 20
l 21
m 22
n 23
o 24
p 25
q 26
r 27
s 28
t 29
u 30
v 31
w 32
x 33
y 34
z 35
- 36
. 37
38
$ 39
/ 40
+ 41
% 42

[charltondav]

I would expect +H90611210570/$11000000 to return +H90611210570/$11000000% using that formula.

[shg]

Without thinking too hard,

=IFERROR(MID("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%",
MOD(SUMPRODUCT(SEARCH(MID(A1,ROW($1:$99),1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%")) - 98, 43), 1), "%")

[charltondav]

Thanks. That's a simple fix. I'd still like to know why the formula wasn't working, but this will work.

Thank you.

[shg]

Watch the formula evaluate with just the percent symbol in a cell and you'll see why.

[FlameRetired]

Another approach.

Please Login or Register  to view this content.

[shg]

Much better.