# GTIN-14 Check Digit formula

#### KLOR

##### New Member
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)

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### KLOR

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

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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### New Member
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

##### New Member
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

##### New Member
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.