Calculating UPC Barcode Check Digits

[ktothek]

Hello everyone,

I am new at the forum, and have a tricky question.

My company uses UPC codes for identifying its products. As you might know, the UPC codes have 11 digits + 1 digit check code. And the formulation to calculate this check digit is as following:

Step One: From the right to the left, start with odd position, assign the odd/even position to each digit.

Step Two: Sum all digits in odd position and multiply the result by 3. (7+6+1+1+5+2)*3=66
Step Three: Sum all digits in even position. (2+4+2+7+4)=19
Step Four: Sum the results of step three and four: 66+19=85
Step Five: Divide the result of step four by 10. The check digit is the number which adds the remainder to 10. In our case, divide 85 by 10 we get the remainder 5. The check digit then is the result of 10-5=5.



So, what I did to calculate this was like that:
1. Cell A1 is the eleven digit number: 72641217542
2. Cells A2 to A12 are 7, 2, 6, 4, 1, 2, 1, 7 ,5, 4, 2 correspondingly.
3. Cell A13 has the following formula to calculate the check digit:

=RIGHT(ROUNDDOWN((10-((((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10)-ROUNDDOWN((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10,0))*10)),0),1)


4. Cell A14 has the following formula to combine whole:

=CONCATENATE(A1,A13)


I know it is complicated but it works perfectly. Could you guys advice me any other shorter ways to do that?

Thanks in advance,

Ray

 

[mbon91]

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.

 

[excel_wanabee]

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]

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]

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)

 

[fming]

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]

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]

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