Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculating Check Digit in UPC?


Recommended Posts

Hi, all. I've searched for this one high and low and can't come up with anything.


I am trying to figure out how to enter the 11 digits of a UPC A code in one field, and have another field calculate the check digit. UPC format is this:


N1 - N2N3N4N5N6 - N7N8N9N10N11 - N12


N12 is the check digit.


To calculate a check digit you go through a three step process.


Step 1 - Multiply the value of each position by a number (e.g. N1 x 3, N2 x 1, N3 x 3, etc.)


Step 2 - Sum of all those calculated values


Step 3 - Subtract the sum from nearest equal or higher multiple of ten. The result is the check digit.


There are ways to break this up. Example, all of the odd position numbers are multiplied by three, and all of the even position numbers are multiplied by one. So, you can break this into two calculations right there (I have this in Excel as [=(SUM(B2,D2,F2,H2,J2,L2))*3] and [=SUM(C2,E2,G2,I2,K2)] in a row where N1 is in cell B2).


I imagine steps 1 & 2 being fairly straightfoward, I just don't know which formula to start with. Step 3 is the doozy for me.


Any suggestions appreciated.





Link to comment
Share on other sites

Actually...there's an error in the formula. The last step in the calculation is "10 minus [the right digit of previous sum]." But if that right digit is zero, then the result in the check digit field is 10, which is not a viable check digit.


Calculation text for that step is:


step5 = 10 - Right ( step4 ; 1 )


How to abbreviate that to just the right digit of the result?

Link to comment
Share on other sites

Let ([

Step1 = Right ("00000000000" & UPC; 11);

Step2 = Middle(Step1;1;1) + Middle(Step1;3;1) + Middle(Step1;5;1) + Middle(Step1;7;1) + Middle(Step1;9;1) +Middle(Step1;11;1);

Step3 = Step2 * 3;

Step4 = Middle(Step1;2;1) + Middle(Step1;4;1) + Middle(Step1;6;1) + Middle(Step1;8;1) + Middle(Step1;10;1);

Step5 = Step3 + Step4;

Step6 = If(Right(Step5;1) > 0; 10 - Right(Step5;1); 0)




Check against http://www.uc-council.org/ean_ucc_system/education_support/cdc.html to make sure I got everything correctly.

Link to comment
Share on other sites

Thanks. What I did was just insert a step 6 that grabs the right digit of step 5, regardless of the outcome. (If step 5 = 10, we're good. If step 5 = any single digit, we're good.)


Thanks to both for your help.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use