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.

Best,

VA

##### Share on other sites

This thread contains a sample file that describes the process.

--Doug

##### Share on other sites

I don't know how I didn't come across that thread. Thank you very much.

##### Share on other sites

Sheesh, I'm not nearly as smart as I thought. 10 minus the right digit. Couldn't be easier.

Thank you.

##### 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?

##### Share on other sites

Nevermind, I figured it out. Thanks again.

##### 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)

];

Step6)

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

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

##### Share on other sites

Also, how to format the field in the layouts so that the data is stored as just the 12 digits but displayed as #-#####-#####-#?

##### Share on other sites

Left(GetAsText ( number );1)&"-"&Middle(GetAsText ( number );2;5) & "-" & Middle(GetAsText ( number );7;5) & "-" & Right(GetAsText ( number );1)

## Join the conversation

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

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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

×

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...