Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Barcode Check Digits
I have a spreadsheet full of GTIN - 13 barcode id numbers without check
digits. I can calculate the check digit for each but I need to know if anyone has created a formula to auto calculate each in Excel? The process for calculating a GTIN-13 barcode check digit is listed below unfortunately I am not familiar enough with the Excel formulas to even know where to start. 1. Suppose you want to find the Check Digit for the GTIN-13 or GLN ID Number 101454121022. Set up a table with 13 columns, and put the number 101454121022 into Positions One through Twelve. Position Thirteen will be blank because it is reserved for the Check Digit. 2. Add the numbers in Positions Two, Four, Six, Eight, Ten, and Twelve: (0 + 4 + 4 + 2 + 0 + 2 = 12). 3. Multiply the result of Step Two by three: (12 x 3 = 36). 4. Add the numbers in Positions One, Three, Five, Seven, Nine, and Eleven: (1 + 1 + 5 + 1 + 1 + 2 = 11). 5. Add the results of Step Three and Step Four: (36 + 11 = 47). 6. The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 3. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Barcode Check Digits
If the bar code is in cell A1, try this for the last digit:
=MOD(-SUM(MID(A1,{2,4,6,8,10,12;1,3,5,7,9,11},1)*{3;1}), 10) On Feb 5, 4:19 pm, Doug B <Doug wrote: I have a spreadsheet full of GTIN - 13 barcode id numbers without check digits. I can calculate the check digit for each but I need to know if anyone has created a formula to auto calculate each in Excel? The process for calculating a GTIN-13 barcode check digit is listed below unfortunately I am not familiar enough with the Excel formulas to even know where to start. 1. Suppose you want to find the Check Digit for the GTIN-13 or GLN ID Number 101454121022. Set up a table with 13 columns, and put the number 101454121022 into Positions One through Twelve. Position Thirteen will be blank because it is reserved for the Check Digit. 2. Add the numbers in Positions Two, Four, Six, Eight, Ten, and Twelve: (0 + 4 + 4 + 2 + 0 + 2 = 12). 3. Multiply the result of Step Two by three: (12 x 3 = 36). 4. Add the numbers in Positions One, Three, Five, Seven, Nine, and Eleven: (1 + 1 + 5 + 1 + 1 + 2 = 11). 5. Add the results of Step Three and Step Four: (36 + 11 = 47). 6. The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 3. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Barcode Check Digits
Thank you, Lori
That worked great! "Lori" wrote: If the bar code is in cell A1, try this for the last digit: =MOD(-SUM(MID(A1,{2,4,6,8,10,12;1,3,5,7,9,11},1)*{3;1}), 10) On Feb 5, 4:19 pm, Doug B <Doug wrote: I have a spreadsheet full of GTIN - 13 barcode id numbers without check digits. I can calculate the check digit for each but I need to know if anyone has created a formula to auto calculate each in Excel? The process for calculating a GTIN-13 barcode check digit is listed below unfortunately I am not familiar enough with the Excel formulas to even know where to start. 1. Suppose you want to find the Check Digit for the GTIN-13 or GLN ID Number 101454121022. Set up a table with 13 columns, and put the number 101454121022 into Positions One through Twelve. Position Thirteen will be blank because it is reserved for the Check Digit. 2. Add the numbers in Positions Two, Four, Six, Eight, Ten, and Twelve: (0 + 4 + 4 + 2 + 0 + 2 = 12). 3. Multiply the result of Step Two by three: (12 x 3 = 36). 4. Add the numbers in Positions One, Three, Five, Seven, Nine, and Eleven: (1 + 1 + 5 + 1 + 1 + 2 = 11). 5. Add the results of Step Three and Step Four: (36 + 11 = 47). 6. The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 3. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Barcode Check Digits
Lori
What would the formula look like for a GTIN 14? See calculation procedure below... Step One: Suppose you want to find the Check Digit for the GTIN-14 ID Number 9101454121022. Set up a table with 14 columns, and put the number 9101454121022 into Positions One through Thirteen. Position Fourteen will be blank because it is reserved for the Check Digit. Step Two: Add the numbers in Positions One, Three, Five, Seven, Nine, Eleven, and Thirteen: (9 + 0 + 4 + 4 + 2 + 0 + 2 = 21). Step Three: Multiply the result of Step Two by three: (21 x 3 = 63). Step Four: Add the numbers in Positions Two, Four, Six, Eight, Ten, and Twelve: (1 + 1 + 5 + 1 + 1 + 2 = 11). Step Five: Add the results of Step Three and Step Four: (63 + 11 = 74). Step Six: The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 6. *************************** "Lori" wrote: If the bar code is in cell A1, try this for the last digit: =MOD(-SUM(MID(A1,{2,4,6,8,10,12;1,3,5,7,9,11},1)*{3;1}), 10) On Feb 5, 4:19 pm, Doug B <Doug wrote: I have a spreadsheet full of GTIN - 13 barcode id numbers without check digits. I can calculate the check digit for each but I need to know if anyone has created a formula to auto calculate each in Excel? The process for calculating a GTIN-13 barcode check digit is listed below unfortunately I am not familiar enough with the Excel formulas to even know where to start. 1. Suppose you want to find the Check Digit for the GTIN-13 or GLN ID Number 101454121022. Set up a table with 13 columns, and put the number 101454121022 into Positions One through Twelve. Position Thirteen will be blank because it is reserved for the Check Digit. 2. Add the numbers in Positions Two, Four, Six, Eight, Ten, and Twelve: (0 + 4 + 4 + 2 + 0 + 2 = 12). 3. Multiply the result of Step Two by three: (12 x 3 = 36). 4. Add the numbers in Positions One, Three, Five, Seven, Nine, and Eleven: (1 + 1 + 5 + 1 + 1 + 2 = 11). 5. Add the results of Step Three and Step Four: (36 + 11 = 47). 6. The Check Digit is the smallest number needed to round the result of Step Five up to a multiple of 10. In this example, the Check Digit is 3. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of digits in a cell | Excel Worksheet Functions | |||
Linked Check Boxes | Excel Discussion (Misc queries) | |||
Can Not Check more than 1 check box | Excel Discussion (Misc queries) | |||
... Can I set Spell Check to automatically check my spelling ... | Setting up and Configuration of Excel | |||
Mod 10 & 11 | Excel Worksheet Functions |