G
Guest
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.)
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
I
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.
digit.)
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.
character EAN code from the leading 12 characters? (The 13th is the chaeckStephen Brown said:Has anyone written a formula to calculate the check digit for a 13
digit.)
M
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
check digit.
Verify it here: http://www.ean-int.org/cdcalcul.html
I
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.
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.
G
Guest
Thanks.
Only one problem. If the check dgit should be 0 the formula produces '10' instead
Only one problem. If the check dgit should be 0 the formula produces '10' instead
H
Harlan Grove
...
...
There are shorter versions available in the archives.
http://www.google.com/groups?selm=#CUrvvURBHA.1628@tkmsftngp05
...
...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
D
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.
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.
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
That's my favorite way to learn new things in Excel.
Thanks to all you experts out there for exercising our newbie brains!
Markus
T
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
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