Calculating Check Digit in UPC?

[VA12]

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

[doughemi]

This thread contains a sample file that describes the process.

 

--Doug

[VA12]

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

[VA12]

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

 

Thank you.

[VA12]

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?

[VA12]

Nevermind, I figured it out. Thanks again.

[sbg2]

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.

[VA12]

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.

[VA12]

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

[sujat]

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