EAN-13 checksum

  • Thread starter Thread starter Don Quai
  • Start date Start date
D

Don Quai

I want to create a function that will allow me to enter in the first 12
numbers of an EAN-13 barcode and have the function calculate the checksum
and enter it into the appropriate cell. Any ideas?
 
Place the following into a standard module, you can then get the EAN13 check
digit by entering the following within a worksheet cell the function name
followed by the EAN13 number. I have not added any error checking to ensure
that the first part of the EAN number is valid. But the country,
manufacturer, product code could be checked as part of the function.

e.g.

=EAN13cd(123456789123)



Function EAN13cd(Base As Variant) As Integer
Z = 0: Odd = True
For x = Len(Base) To 1 Step -1
If Odd Then
Z = Z + Mid(Base, x, 1) * 3
Odd = False
Else
Z = Z + Mid(Base, x, 1)
Odd = True
End If
Next
EAN13cd = 10 - Z Mod 10
If EAN13cd = 10 Then EAN13cd = 0
End Function
 
Or you can use this formula:
=CEILING(((MID(A2,2,1)+MID(A2,4,1)+MID(A2,6,1)+MID(A2,8,1)+MID(A2,10,1)+MID(A2,12,1))*3)+(MID(A2,1,1)+MID(A2,3,1)+MID(A2,5,1)+MID(A2,7,1)+MID(A2,9,1)+MID(A2,11,1)),10)-(((MID(A2,2,1)+MID(A2,4,1)+MID(A2,6,1)+MID(A2,8,1)+MID(A2,10,1)+MID(A2,12,1))*3)+(MID(A2,1,1)+MID(A2,3,1)+MID(A2,5,1)+MID(A2,7,1)+MID(A2,9,1)+MID(A2,11,1)))

Regards,
Stefi

„Nigel†ezt írta:
 
Caution with the formulaic approach in that if the EAN13 code length is less
than 12, which occurs if the leading zeros are not entered or suppressed as
it appears as a numerical value, an error will occur.

--

Regards,
Nigel
(e-mail address removed)
 
Don said:
I want to create a function that will allow me to enter in the first 12
numbers of an EAN-13 barcode and have the function calculate the checksum
and enter it into the appropriate cell. Any ideas?
Click to expand...
Dang you folks are quick. Thanks a bunch for the ideas. I just realized
that some extra info might be beneficial to your answering my query. I
am running Excel 2002. Will these formulas work in this version? Also is
there anyway to modify these formulas so that it will also check for a
duplicate in the worksheet and alert me? I hope you will understand my
question.

Don Eitner
 
The function I provided should work in Excel 97 onwards, I have tested it in
Excel 2003 and Excel 2007.

Duplication of these numbers or anything else is best dealt with outside of
this function and any formulaic approach.

The best method depends entirely on the data structure, but I guess you have
a column of EAN13 numbers and related rows of data you wish to check? To
simply get a unique list use the Advanced Filter option, checking the option
to copy to another location and click unique records only. Set other
settings to suit your data.

Other methods are possible, if you wish to automate the above, use the macro
recorder. The resulting code will need to be adapted, especially if you have
varying lengths of data to manage.


--

Regards,
Nigel
(e-mail address removed)
 
Yes, but I think that codes are to be handled as text even if they consists
of purely digits. Leading zeros are as important parts of codes as any other
of their characters.
Stefi


„Nigel†ezt írta:
 
One method for alerting in case of duplication:
Say your EAN codes are in column A, apply conditional formatting with formula
=COUNTIF($A:$A,A1)>1
for column A with, say red color pattern. Duplicate items will turn to red!

Regards,
Stefi


„Nigel†ezt írta:
 
Thanks! Not my invention! Credit is to be given this newsgroup for it!
Stefi


„Nigel†ezt írta:
 
Back
Top