GTIN-14 Check Digit formula

[KLOR]

I'm trying to calculate the check digit for a 13 digit upc. I found this formula, but it only allows up to 12 digits. When I try to add 13 and 14 so the formula calculates those digits also, the formula returns as "INVALID". Please help!

Number: 1076419513111 (should calc check digit 2)

Formula: =MOD(-SUM(MID(B1,{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)
(results w/ 5, which is correct for a 12 digit number, but I need 13)

 

[KLOR]

After numerous search for this answer on the web, I found it myself. Here it is for anyone with the same frustration and question:

Web:http://www.adams1.com/upccode.html
Note: $A1 is your referencing cell.
Formula: =10-MOD(MID($A1,2,1)+MID($A1,4,1)+MID($A1,6,1)+MID($A1,8,1)+MID($A1,10,1)+MID($A1,12,1)+(MID($A1,1,1)+MID($A1,3,1)+MID($A1,5,1)+MID($A1,7,1)+MID($A1,9,1)+MID($A1,11,1)+MID($A1,13,1))*3,10)

 

[JPereyra]

I needed to modifiy your formula slightly because when using your formula on UPC-13 "1030521275000" I keep geting a check digit of "10". So I had to add the =RIGHT(?,1) function to only get a single digit per below....

{Reference cell A2):

=RIGHT((10-MOD(MID($A2,2,1)+MID($A2,4,1)+MID($A2,6,1)+MID($A2,8,1)+MID($A2,10,1)+MID($A2,12,1)+(MID($A2,1,1)+MID($A2,3,1)+MID($A2,5,1)+MID($A2,7,1)+MID($A2,9,1)+MID($A2,11,1)+MID($A2,13,1))*3,10)),1)

 

[fairwinds]

Hi,

Would not the original formula work if you just change it a bit?

=MOD(-SUM(MID(B1&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}),10)

 

[KLOR]

fairwinds and JPereya

Thanks for the heads up and shortened formula. I did find some errors on our list of UPCS. When a check digit ought to be "0", it was coming out "10".... This is being corrected today.

Much appreciated!

 

[VishMishra]

Hi,

you can use this formula:

="00" & A1 & 10 - MOD( SUMPRODUCT(MID(A1, {1,2,3,4,5,6,7,8,9,10,11},
1) * {3,1,3,1,3,1,3,1,3,1,3}), 10)

if you want i have one already built Excel workboook for calculating all types of GTIN using macro.

http://www.learnexcelmacro.com/2011/11/gtin-calculator-in-excel/

 

[labelady]

Using all the formulas around here - I arrived at this one - it solves the extra digit issue when the result is 10.
=A2&RIGHT(10-MOD(SUMPRODUCT(MID(A7,{1,2,3,4,5,6,7,8,9,10,11},1)*{3,1,3,1,3,1,3,1,3,1,3}),10))

 

[md_ismail]

The above formula doesn't calculate check digit. There is an error.

I have seen the same problem in Freelancer. Someone "lucamagri183" from Freelancer has the same issue. I have the formula and template ready. Would be glad if they can hire me ( ismailindia is my id ) on Freelancer to solve this.

Looking forward for kind cooperation.


Thanks & Regards


Mohammed Ismail

 

[corky29]

Thanks for the useful information in this thread. I used it to create a formula to derive the GTIN-14 from the GTIN-13;

=CONCATENATE(1,LEFT(A1,12),MOD(-SUM(MID((CONCATENATE(1,LEFT(A1,12)))&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}),10))

The GTIN-13 should go in Cell A1 for the above to work.

 

[jaykup]

For anyone else coming across this via google, I can confirm that JPereyra's formula works and solves the 10 problem.

=RIGHT((10-MOD(MID($A2,2,1)+MID($A2,4,1)+MID($A2,6,1)+MID($A2,8,1)+MID($A2,10,1)+MID($A2,12,1)+(MID($A2,1,1)+MID($A2,3,1)+MID($A2,5,1)+MID($A2,7,1)+MID($A2,9,1)+MID($A2,11,1)+MID($A2,13,1))*3,10)),1)