EAN Check Digit Calculation

[Stephen Brown]

Has anyone written a formula to calculate the check digit for a 13 character EAN code from the leading 12 characters? (The 13th is the chaeck digit.)

 

[immanuel]

With your 12 characters in A1, use the following array formula to calculate
the check digit:

=MOD(SUM(VALUE(MID(TEXT($A$1,"000000000000"),12-ROW(INDIRECT("1:12"))+1,1))*
IF(MOD(ROW(INDIRECT("1:12")),2),3,1)),10)

Remember to array-enter the formula. (Instead of hitting Enter after typing
in the formula, hit Ctrl-Shift-Enter.)

/i.

Has anyone written a formula to calculate the check digit for a 13

character EAN code from the leading 12 characters? (The 13th is the chaeck
digit.)

 

[Markus L]

Subtract the result of immanuel's formula from 10 to obtain the correct
check digit.
Verify it here: http://www.ean-int.org/cdcalcul.html

 

[immanuel]

Wow. Thanks for the correction, Markus L.

So the formula should read:

=10-MOD(SUM(VALUE(MID(TEXT(A1,"000000000000"),12-ROW(INDIRECT("1:12"))+1,1))
*IF(MOD(ROW(INDIRECT("1:12")),2),3,1)),10)

/i.

 

[Stephen Brown]

Thanks.

Only one problem. If the check dgit should be 0 the formula produces '10' instead

 

[Harlan Grove]

...
...

So the formula should read:

=10-MOD(SUM(VALUE(MID(TEXT(A1,"000000000000"),12-ROW(INDIRECT("1:12"))+1,1))
*IF(MOD(ROW(INDIRECT("1:12")),2),3,1)),10)

...

There are shorter versions available in the archives.

http://www.google.com/groups?selm=#CUrvvURBHA.1628@tkmsftngp05

 

[Daniel.M]

Hi,

If I understood the example provided on the link (888 6451 73885 giving 1):

=MOD(SUM(10,-MID(A1,{1;3;5;7;9;11}+{1,0},1)*{3,1}),10)

Regards,

Daniel M.

 

[Markus L]

Daniel, that's a good one! Took me quite a bit of time to understand it...
That's my favorite way to learn new things in Excel.
Thanks to all you experts out there for exercising our newbie brains!

Markus

 

[Thomas Lutz]

The following VBA function will calculate the check digit for a EAN13
12 digit message.
It sounds like you are using a bar code font. With EAN bar codes, you
also need start and stop codes as well as a center guard pattern.
Even if you do all of this, fonts still produce very porr quality bar
codes. If you want excellent bar codes without having to calculate
check digits or add start/stop/guard patterns, you would be better off
using a bar code ActiveX control. The best one on the market can be
found at:
http://www.taltech.com/products/activex_barcodes.html
There is a demo at the above URL that includes a sample Excel sheet
that shows how to use the ActiveX control in Excel.


Function EAN13CheckDigit(Msg$) As String

For X& = 1 To Len(Msg$) ' should be 12 digits
Test$ = Mid$(Msg$, X&, 1)
Select Case X& ' calculate check digit
Case 1, 3, 5, 7, 9, 11
Check& = Check& + Val(Test$) * 9
Case 10, 2, 4, 6, 8, 12
Check& = Check& + Val(Test$) * 7
End Select
Next
Check& = (Check& Mod 10) + 48
EAN13CheckDigit = Chr$(Check&)
End Function