# Calculating UPC Barcode Check Digits

#### facethegod

##### Well-known Member
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

### 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
=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
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
Andrew - I never used MOD. Never had to. Will look into it though.

Kurt

#### stapuff

##### Well-known Member
Can you use MOD with the example above as a single cell?

#### Andrew Fergus

##### MrExcel MVP
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
 =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
 =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
Under what circumstances do the other solutions not work? Or what renders them "almost correct"?

#### excel_wanabee

##### New Member
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.