Calculating UPC Barcode Check Digits

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
Using the MID approach....

UPC in A1, formula in B1:

=A1&(10-MOD(SUM(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)=0,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,3*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),10))

Confirm with Control+Shift+Enter



HTH
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
=concatenate(a1,10-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)))
 
Last edited:

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Why are you using the rounddown? The mod function gives the remainder which is what you want. If there is a floating point error then you may get a different result.

Andrew
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
Andrew - I never used MOD. Never had to. Will look into it though.

Kurt
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
Can you use MOD with the example above as a single cell?
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Using your example, how about something like this:

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

Andrew
 

mbon91

New Member
Joined
Dec 15, 2014
Messages
2
=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))))

<tbody>
</tbody>

This will calculate correctly. Other suggestions at best would produce almost correct solutions.
 

excel_wanabee

New Member
Joined
Aug 26, 2014
Messages
5
=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))))

<tbody>
</tbody>

This will calculate correctly. Other suggestions at best would produce almost correct solutions.
I know it's bad form to reopen a dead thread, BUT....mbon91 is right.

I've found a lot of supposed solutions for a check digit calculator in this forum. I think I've tried them all. None of them except the one above calculates the correct solution every single time. I have not found an instance when the above formula's check digit doesn't exactly match the one calculated at GS1 (the authority) Check digit calculator | GS1.

Now to my question, this formula is WAY beyond my ability to even barely comprehend it. This formula provides the complete 12-digit UPC. How can I modify this formula to return only the check digit instead of putting it all together?
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Under what circumstances do the other solutions not work? Or what renders them "almost correct"?
 

excel_wanabee

New Member
Joined
Aug 26, 2014
Messages
5
Under what circumstances do the other solutions not work? Or what renders them "almost correct"?
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,621
Messages
5,445,534
Members
405,337
Latest member
kann82

This Week's Hot Topics

Top