Calculating UPC Barcode Check Digits

mbon91

New Member
Joined
Dec 15, 2014
Messages
2
No idea. I can't understand the formulas let alone the math logic within them. I just tried each of the formulas I found here and around the internet against a list of 100 known correct/complete UPCs that I removed the check digit from. I used the GS1 calculator to confirm which was correct when there was a conflict. The above formula was correct 100% of the time.
=right(IF(RIGHT(CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8 ,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))),2)="10",LEFT(CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8 ,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1))),11)&"0",CONCATENATE(A3,10-RIGHT(ROUNDDOWN((SUM(RIGHT(A3,1)+MID(A3,9,1)+MID(A3,7,1)+MID(A3,5,1)+MID(A3,3,1)+LEFT(A3,1))*3+SUM(MID(A3,10,1)+MID(A3,8 ,1)+MID(A3,6,1)+MID(A3,4,1)+MID(A3,2,1))),1)))),1)

This should return just the check digit.

The other formulas didn't accurately calculate when the check digit was equal to "0." They would return "10" instead and have 13 digits total.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

excel_wanabee

New Member
Joined
Aug 26, 2014
Messages
5
Perfect. Thanks!

The formulas in this thread may have had the problem with a 0 check digit. Others around the forum and internet, now that I look back at my testing sheet, seem to have issues with how the final remainder is reached because the check digit seemed to always be off by 1 -- higher or lower than the correct check digit. Hope this "thread extension" helps everyone else googling this solution.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
This is a condensed version that fixes the 10 vs 0 issue:

=MOD(10-MOD((SUM(RIGHT(A1,1) + MID(A1,9,1) + MID(A1,7,1) + MID(A1,5,1) + MID(A1,3,1) + LEFT(A1,1))*3 + SUM(MID(A1,10,1) + MID(A1,8,1) + MID(A1,6,1) + MID(A1,4,1) + MID(A1, 2, 1))), 10),10)
 

fming

New Member
Joined
Nov 25, 2015
Messages
3
This is an old post, but ....

My Check Digit Calculator is based on an 11-Digit UPC-A Code without the check-digit. The prefix is there, we will ADD the Check Digit. Sample image is below.



Cell D3 has the 11 digit UPC-A code

=10-(MOD(ROUND((
((MID(D3,1,1)+MID(D3,3,1)+MID(D3,5,1)+MID(D3,7,1)+MID(D3,9,1)+MID(D3,11,1))*3)
+(MID(D3,2,1)+MID(D3,4,1)+MID(D3,6,1)+MID(D3,8,1)+MID(D3,10,1))
)/10,1),1)*10)


This is bascially the following formula:
10 - (MOD((((SUM_ODDS x 3) + (SUM_EVENS)) /10),1) * 10)


It is based on the Steps below

Steps

S1: Sum all ODD digits & multiply by 3.
formula: S1=(MID(D3,1,1)+MID(D3,3,1)+MID(D3,5,1)+MID(D3,7,1)+MID(D3,9,1)+MID(D3,11,1))*3


S2: Sum all EVEN digits.
formula: S2=(MID(D3,2,1)+MID(D3,4,1)+MID(D3,6,1)+MID(D3,8,1)+MID(D3,10,1))


S3: Add results of S1 and S2
formula: S3=S1 + S2


S4: Divide the result of S3 by 10.
formula: S4=ROUND(S3/10,1)


S5: Multiply fraction of S4 (should be tenths) by 10.
formula: S5=MOD(s4,1) * 10


S6: Check Digit is 10 minus S5
formula: s6=10-S5



IMPORTANT Notes:
1. Determine if a digit is in an ODD or EVEN position by counting from the left. Since I only have 11 digits, it 's simple.
2. Rounding is needed because of Excel
3. Any number modulo 1 will return the fractional part. You can also calculate it by X-TRUNC(X)
 
Last edited:

fming

New Member
Joined
Nov 25, 2015
Messages
3
Modified formula
=IFERROR(RIGHT(10-(MOD(ROUND((
((MID(D2,1,1)+MID(D2,3,1)+MID(D2,5,1)+MID(D2,7,1)+MID(D2,9,1)+MID(D2,11,1))*3)
+(MID(D2,2,1)+MID(D2,4,1)+MID(D2,6,1)+MID(D2,8,1)+MID(D2,10,1))
)/10,1),1)*10)),0)

IMPORTANT Notes:
1. Determine if a digit is in an ODD or EVEN position by counting from the left. Since this is only 11 digits, we're the 1st digit is odd.
2. Rounding is needed becase of excel.
3. Any number modulo 1 will return the fractional part. You can also calulate it by X-TRUNC(X)
4. The RIGHT funtion will return 0 - the right most character - if the Check Digit is 10, otherwise it will return the Check Digit.
5. The IFERROR function is used to return 0 if the 11-digit UPC-A code is missing.
 

jlarkin

New Member
Joined
Oct 25, 2017
Messages
1
Hey guys, for the remainder, you should be using the MOD() function and check out what happens when you take the MOD of a negative number.

I came across this elegant solution elsewhere on MrExcel.com:
=MOD(-SUM(MID(A1,{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)

Beautiful!

If your cell contains a number instead of text, try this first TEXT(A1,"000000000000") so it is:
=MOD(-SUM(MID(TEXT(A1,"000000000000"),{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
How 'bout a formula?

Function UPC_CheckDigit(r As Range) As String
'
' Calculate Check Digit
'
'
Dim i As Integer
Dim TotalOdd As Integer
Dim TotalEven As Integer
Dim Total As Integer
Barcode = Trim(r.Formula)
vEO = Len(Barcode) Mod 2
If vEO = 0 Then
'EVEN - get set 1
For i = 2 To Len(Barcode) Step 2
TotalOdd = TotalOdd + CInt(Mid(Barcode, i, 1))
Next i
TotalOdd = TotalOdd * 3
'EVEN - get set 2
i = 0
For i = 1 To Len(Barcode) Step 2
TotalEven = TotalEven + CInt(Mid(Barcode, i, 1))
Next i
Else
'ODD - get set 1
For i = 1 To Len(Barcode) Step 2
TotalOdd = TotalOdd + CInt(Mid(Barcode, i, 1))
Next i
TotalOdd = TotalOdd * 3
'ODD - get set 2
i = 0
For i = 2 To Len(Barcode) Step 2
TotalEven = TotalEven + CInt(Mid(Barcode, i, 1))
Next i
End If
Total = TotalOdd + TotalEven
UPC_CheckDigit = 10 - IIf(Right(Total, 1) = 0, 10, Right(Total, 1))
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,095,623
Messages
5,445,557
Members
405,339
Latest member
dayanand008

This Week's Hot Topics

Top