Calculating UPC Barcode Check Digits

ktothek

New Member
Joined
Mar 20, 2008
Messages
4
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
 
Last edited:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

custardcrumble

Board Regular
Joined
Jul 6, 2004
Messages
160
Welcometo the board!

There must be a better way of getting the Odds/Evens but this is a little shorter:

Code:
=CONCATENATE(A2,10-MOD(((B2+D2+F2+H2+J2+L2)*3)+(C2+E2+G2+I2+K2),10))
A2 is the 11 digit number and B2:L2 are the individual digits
HTH
CC
 

ktothek

New Member
Joined
Mar 20, 2008
Messages
4
Thanks for your answer.

I gave that a try but it calculates some of them but gives wrong numbers for somes. Check that number both manually and with your formula:

84479400005

It should be 6 but your formula calculates 7.

Maybe a slight correction in the formula could correct it.

Thanks again.

Ray
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
Put the UPC # in A1

Put the following in B1 =CONCATENATE(A1,RIGHT(ROUNDDOWN((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))),1)))

Kurt
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
I independently came up with the same formula as custardcrumble, being:
=10-MOD(((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11),10)

Manually check your maths on 84479400005 - I believe the answer should be 7 per your instructions in your first post.

Andrew
 

custardcrumble

Board Regular
Joined
Jul 6, 2004
Messages
160
Hmm, I've done it manualy and get 7...
Odds: 8+4+9+0+0+5=26, times 3=78
Evens: 4+7+4+0+0=15
Sum=93
Remainder = 3
10-Remainder=7...

Or have I misinterpreted your description?
CC
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Kurt

I think you left out the 10- part.

Andrew
 

custardcrumble

Board Regular
Joined
Jul 6, 2004
Messages
160
Agree with stapuff about using MID to save writing out the digits separately or you could write a simple macro to do it for you. Depends how easy to read you want your formula!

CC :)
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
the rounddown right 1 of the result automatically gives you the remainder, doesn't it?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,230
Messages
5,443,233
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top