|
EAN-13 checksum
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? |
EAN-13 checksum
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? |
EAN-13 checksum
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? |
EAN-13 checksum
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 |
EAN-13 checksum
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 |
EAN-13 checksum
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? |
EAN-13 checksum
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 |
EAN-13 checksum
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 |
EAN-13 checksum
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 |
| All times are GMT +1. The time now is 09:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com