GTIN Calculation is basically of different types like GTIN 8, GTIN 12, GTIN 13 and GTIN 14. We can calculate GTIN by using Excel Formula as well as Excel Macro.
GTIN Calculator By Formula | GTIN Calculator By Macro | How to Calculate GTIN | Download GTIN Calculator
GTIN Calculation- By Excel Formula:
GTIN-8 | "000000"&F11 & (IF(MOD(3*(MID(F11,1,1) + MID(F11,3,1) + MID(F11,5,1) + MID(F11,7,1))+(MID(F11,2,1) + MID(F11,4,1) + MID(F11,6,1)),10)=0,0,((QUOTIENT(3*(MID(F11,1,1) + MID(F11,3,1) + MID(F11,5,1) + MID(F11,7,1))+(MID(F11,2,1) + MID(F11,4,1) + MID(F11,6,1)),10)+1)*10)-(3*(MID(F11,1,1) + MID(F11,3,1) + MID(F11,5,1) + MID(F11,7,1))+(MID(F11,2,1) + MID(F11,4,1) + MID(F11,6,1))))) |
GTIN-12 | "00"&F12 & (IF(MOD(3*(MID(F12,1,1) + MID(F12,3,1) + MID(F12,5,1) + MID(F12,7,1) + MID(F12,9,1) + MID(F12,11,1))+(MID(F12,2,1) + MID(F12,4,1) + MID(F12,6,1) + MID(F12,8,1) + MID(F12,10,1)),10)=0,0,((QUOTIENT(3*(MID(F12,1,1) + MID(F12,3,1) + MID(F12,5,1) + MID(F12,7,1) + MID(F12,9,1) + MID(F12,11,1))+(MID(F12,2,1) + MID(F12,4,1) + MID(F12,6,1) + MID(F12,8,1) + MID(F12,10,1)),10)+1)*10)-(3*(MID(F12,1,1) + MID(F12,3,1) + MID(F12,5,1) + MID(F12,7,1) + MID(F12,9,1) + MID(F12,11,1))+(MID(F12,2,1) + MID(F12,4,1) + MID(F12,6,1) + MID(F12,8,1) + MID(F12,10,1))))) |
GTIN-13 | "0"&F13 & (IF(MOD((MID(F13,1,1) + MID(F13,3,1) + MID(F13,5,1) + MID(F13,7,1) + MID(F13,9,1) + MID(F13,11,1))+3*(MID(F13,2,1) + MID(F13,4,1) + MID(F13,6,1) + MID(F13,8,1) + MID(F13,10,1) + MID(F13,12,1)),10)=0,0,((QUOTIENT((MID(F13,1,1) + MID(F13,3,1) + MID(F13,5,1) + MID(F13,7,1) + MID(F13,9,1) + MID(F13,11,1))+3*(MID(F13,2,1) + MID(F13,4,1) + MID(F13,6,1) + MID(F13,8,1) + MID(F13,10,1) + MID(F13,12,1)),10)+1)*10)-((MID(F13,1,1) + MID(F13,3,1) + MID(F13,5,1) + MID(F13,7,1) + MID(F13,9,1) + MID(F13,11,1))+3*(MID(F13,2,1) + MID(F13,4,1) + MID(F13,6,1) + MID(F13,8,1) + MID(F13,10,1) + MID(F13,12,1))))) |
GTIN-14 | F14 & (IF(MOD((MID(F14,1,1) + MID(F14,3,1) + MID(F14,5,1) + MID(F14,7,1) + MID(F14,9,1) + MID(F14,11,1)+ MID(F14,13,1))+3*(MID(F14,2,1) + MID(F14,4,1) + MID(F14,6,1) + MID(F14,8,1) + MID(F14,10,1) + MID(F14,12,1)),10)=0,0,((QUOTIENT((MID(F14,1,1) + MID(F14,3,1) + MID(F14,5,1) + MID(F14,7,1) + MID(F14,9,1) + MID(F14,11,1)+ MID(F14,13,1))+3*(MID(F14,2,1) + MID(F14,4,1) + MID(F14,6,1) + MID(F14,8,1) + MID(F14,10,1)+ MID(F14,12,1)),10)+1)*10)-((MID(F14,1,1)+ MID(F14,3,1) + MID(F14,5,1) + MID(F14,7,1) + MID(F14,9,1)+ MID(F14,11,1) + MID(F14,13,1))+3*(MID(F14,2,1)+ MID(F14,4,1)+ MID(F14,6,1)+ MID(F14,8,1)+ MID(F14,10,1)+ MID(F14,12,1))))) |
VBA Code to Calculate GTIN
Sub gtin_cal(normal) Dim n Dim n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12 n1 = CInt(Mid(normal, 1, 1)) n2 = CInt(Mid(normal, 2, 1)) n3 = CInt(Mid(normal, 3, 1)) n4 = CInt(Mid(normal, 4, 1)) n5 = CInt(Mid(normal, 5, 1)) n6 = CInt(Mid(normal, 6, 1)) n7 = CInt(Mid(normal, 7, 1)) n8 = CInt(Mid(normal, 8, 1)) n9 = CInt(Mid(normal, 9, 1)) n10 = CInt(Mid(normal, 10, 1)) n11 = CInt(Mid(normal, 11, 1)) n12 = CInt(Mid(normal, 12, 1)) n = (n1 + n3 + n5 + n7 + n9 + n11) + (n2 + n4 + n6 + n8 + n10 + n12) * 3 q = Int(n / 10) r = n Mod 10 If (r <> 0) Then append_value = (10 * (q + 1)) - n Else append_value = 0 End If gtin_val = "0" & normal & CStr(append_value) End Sub
How to Calculate GTIN
Key Format | Digit positions | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
GTIN-8 | N_{1} | N_{2} | N_{3} | N_{4} | N_{5} | N_{6} | N_{7} | N_{8} | |||||||||||
GTIN-12 | N_{1} | N_{2} | N_{3} | N_{4} | N_{5} | N_{6} | N_{7} | N_{8} | N_{9} | N_{10} | N_{11} | N_{12} | |||||||
GTIN-13 | N_{1} | N_{2} | N_{3} | N_{4} | N_{5} | N_{6} | N_{7} | N_{8} | N_{9} | N_{10} | N_{11} | N_{12} | N_{13} | ||||||
GTIN-14 | N_{1} | N_{2} | N_{3} | N_{4} | N_{5} | N_{6} | N_{7} | N_{8} | N_{9} | N_{10} | N_{11} | N_{12} | N_{13} | N_{14} | |||||
Step 1: Multiply value of each position by 1 0 3 as mentioned below for each of the GTIN | |||||||||||||||||||
3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | |||
Step 2: Add results together to create sum | |||||||||||||||||||
Step 3: Subtract the sum from nearest equal or higher multiple of ten = Check Digit | |||||||||||||||||||
Step 4: Now Append the Check Digit at the End of the Number. Also we need to make the complete Number of 14 Digit, So append the remaining digits as Zero as a Prefix. |
Download this GTIN Calculator
I have created one Excel Workbook which can calculate all types of GTIN. If you want all GTIN calculator in Excel, download by clicking on the below download button.
Thanks for the info. Its very useful , keep on publishing such detailed posts.
Thanks Hitesh,
Keep on giving your valuable feedback and suggestions!!
[…] GTIN Calculator in Excel GTIN Calculation is basically of different types like GTIN 8,… […]
Hitesh,
Ive tried using your formula in an excel sheet as i have 50 check digits i need to calculate and the answer isnt even close to correct when compared to your downloadable one at a time check digit creator.. Any help would be greatly appreciated.
Hitesh,,
I actually have a 14 digit number with the 15th digit being the check digit….
Hi Josh,
Kindly explain me where exactly you are facing the issue. Is it that you are not able to calculate GTIN or is it an invalid GTIN getting generated? Please explain so that I can help you.
I have the same issue. the check digit in my case is the 15th number (not 14th). For example,
example #1&2 with valid check digit.
8862450001240130
8862450001240154
What would the check digit be for the number in between 886245000124014_?
Can you help me with the Excel formula?
thx
Hello sir,
i am creating store management software so i want simple trick for creating software.
Thanks in advance
Hello,
I need an excel template that with help me generate UPC’s in bulk. Can you help?
formula for gtin-14 has the 3 multiplier on the even digit placement instead of the odd digit placement. This results in the incorrect check digit for the formula code.
Thanks Brian for pointing this out. I will have a look ðŸ™‚