Barcode check digit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vass - 03 Dec 2007 11:47 GMT

Anyone got teh check digit formula for Excel
so I might convert a 12 digit barcode number into a check digit'd 13 number
code?
TIA

Signature

Vass
'06 R1
www.doubleyolk.co.uk

Reply to this Message

Vass - 03 Dec 2007 12:11 GMT

> Anyone got teh check digit formula for Excel
> so I might convert a 12 digit barcode number into a check digit'd 13
> number code?

S'ok, I did it,
its nasty but works
=+B20&""&+CEILING(((+(+MID(B20,2,1)+MID(B20,4,1)+MID(B20,6,1)+MID(B20,8,1)+MID(B20,10,1)+MID(B20,12,1))*3)+(+MID(B20,1,1)+MID(B20,3,1)+MID(B20,5,1)+MID(B20,7,1)+MID(B20,9,1)+MID(B20,11,1))),10)-((+(+MID(B20,2,1)+MID(B20,4,1)+MID(B20,6,1)+MID(B20,8,1)+MID(B20,10,1)+MID(B20,12,1))*3)+(+MID(B20,1,1)+MID(B20,3,1)+MID(B20,5,1)+MID(B20,7,1)+MID(B20,9,1)+MID(B20,11,1)))

Signature

Vass

Reply to this Message

Bernard Liengme - 03 Dec 2007 13:37 GMT

Here is one to compute the check digit of an ISBN number
The first digit is multiplied by 10, the next by 9....
These products are summed and divided by 11
If the remainders is 0, the check digit is 0
Otherwise the remainder is subtracted from 11 to give the check digit
(When check digit is 10, the character X is used for the check digit)

=IF(MOD(SUMPRODUCT(VALUE(MID(B26,ROW(A1:A9),1)),{10;9;8;7;6;5;4;3;2}),11)=0,0,11-MOD(SUMPRODUCT(VALUE(MID(B26,ROW(A1:A9),1)),{10;9;8;7;6;5;4;3;2}),11))

I developed this years ago and have never had to use it! So I hope it helps
somebody.
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Anyone got teh check digit formula for Excel
> so I might convert a 12 digit barcode number into a check digit'd 13
> number code?
> TIA

Reply to this Message