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

--

Regards,
Nigel




"Don Quai" wrote in message
...
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?

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,1 1,1)))

Regards,
Stefi

€žNigel€� ezt Ã*rta:

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

--

Regards,
Nigel




"Don Quai" wrote in message
...
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?

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




"Stefi" wrote in message
...
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,1 1,1)))

Regards,
Stefi

€žNigel€� ezt Ã*rta:

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

--

Regards,
Nigel




"Don Quai" wrote in message
...
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?

Don Quai wrote:
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?


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




"Donald Eitner" wrote in message
...
Don Quai wrote:
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?
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

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:

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




"Stefi" wrote in message
...
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,1 1,1)))

Regards,
Stefi

€žNigel€� ezt Ã*rta:

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

--

Regards,
Nigel




"Don Quai" wrote in message
...
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?

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:

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




"Donald Eitner" wrote in message
...
Don Quai wrote:
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?
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

Very nice !

--

Regards,
Nigel




"Stefi" wrote in message
...
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:

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




"Donald Eitner" wrote in message
...
Don Quai wrote:
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?
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

Thanks! Not my invention! Credit is to be given this newsgroup for it!
Stefi


€žNigel€� ezt Ã*rta:

Very nice !

--

Regards,
Nigel




"Stefi" wrote in message
...
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:

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




"Donald Eitner" wrote in message
...
Don Quai wrote:
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?
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