Barcode Check Digits

[Doug B]

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.

 

[Lori]

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)

 

[Doug]

Thank you, Lori
That worked great!

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)

 

[Doug]

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.
***************************