GTIN-14 Check Digit formula

KLOR

New Member
Joined
May 28, 2010
Messages
26
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!:confused:

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)
 

Some videos you may like

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
Joined
May 28, 2010
Messages
26
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

New Member
Joined
Nov 30, 2011
Messages
1
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
Joined
May 15, 2003
Messages
8,638
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
Joined
May 28, 2010
Messages
26
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!
 

labelady

New Member
Joined
Feb 25, 2013
Messages
1
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
Joined
Sep 3, 2015
Messages
1
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
Joined
Jan 11, 2016
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,915
Messages
5,465,423
Members
406,427
Latest member
gboomer

This Week's Hot Topics

Top