UPC Equation

[PneumaGT]

I am new to Excel and I am trying to figure out a fairly comple
equation.

My wife works for a shipping company and they deal with placing UP
codes on various items. Now each item has to have a specific code tha
cannot be shared by any other item (obviously). But the equation sh
has to use in order to do this takes some time. Now the issue here i
that she has hundreds of these UPC Codes she needs to figure out ASAP.
Essentially she just wants to be able to insert the numbers that ar
given to her and have excel figure out the UPC and insert it into th
appropriate field.

Now i have looked at this and it is very possible. But i dont know ho
to write it out (my excel skills are far beyond rusty).

HERE IS WHAT SHE HAS TO DO

Step One: Suppose you want to find the Check Digit for the UCC-1
(U.P.C.) Number 61414121022. Set up a table with 12 columns, and pu
the number 61414121022 into Positions One through Eleven. Positio
Twelve will be blank because it is reserved for the Check Digit.

Step Two: Add the numbers in Positions One, Three, Five, Seven, Nine
and Eleven:

(6 + 4 + 4 + 2 + 0 + 2 = 18).

Step Three: Multiply the result of Step Two by three:

(18 x 3 = 54).

Step Four: Add the numbers in Positions Two, Four, Six, Eight, and Ten


(1 + 1 + 1 + 1 + 2 = 6).

Step Five: Add the results of Step Three and Step Four:

(54 + 6 = 60).

Step Six: The Check Digit is the smallest number needed to round th
result of Step Five up to a multiple of 10. In this example, the Chec
Digit is 0.

The number she would then Have is: 614141210220 I just want her to b
able to type in the number (which is actually pretty arbitrary) an
then have excel do that aforementioned math and spit out that las
number and put that number in the neighboring field.

Ideas

 

[BenjieLop]

<< Step One: Suppose you want to find the Check Digit for the UCC-1
(U.P.C.) Number 61414121022. Set up a table with 12 columns, and pu
the number 61414121022 into Positions One through Eleven. Positio
Twelve will be blank because it is reserved for the Check Digit. >>

FOR THIS EXERCISE, IT WILL BE ASSUMED THAT THE UPC WILL BE ENTERED I
CELL A1[/B]


<< STEP TWO: ADD THE NUMBERS IN POSITIONS ONE, THREE, FIVE, SEVEN
NINE, AND ELEVEN:

(6 + 4 + 4 + 2 + 0 + 2 = 18).


STEP THREE: MULTIPLY THE RESULT OF STEP TWO BY THREE:

(18 X 3 = 54). >>


*THIS FORMULA WILL TAKE CARE OF STEPS 2 AND 3

=SUMPRODUCT(--(MID(A1,{1,3,5,7,9,11},1))*

AND IT CAN BE ENTERED IN, SAY, CELL A


<< Step Four: Add the numbers in Positions Two, Four, Six, Eight, an
Ten:

(1 + 1 + 1 + 1 + 2 = 6). >>


IN CELL A3, ENTER THE FORMUL

=SUMPRODUCT(--(MID(A1,{2,4,6,8,10},1)


<< Step Five: Add the results of Step Three and Step Four:

(54 + 6 = 60). >>

ENTER IN CELL A4:* *=A2 + A


<< Step Six: The Check Digit is the smallest number needed to round th
result of Step Five up to a multiple of 10. In this example, the Chec
Digit is 0.>>

IN CELL A5, ENTE

=IF(MOD(A4,10)=0,0,MOD(A4,10)


<< The number she would then Have is: 614141210220 I just want her t
be able to type in the number (which is actually pretty arbitrary) an
then have excel do that aforementioned math and spit out that las
number and put that number in the neighboring field. >>

ENTER IN CELL A6: =A1&A


_PLEASE_NOTE_:* The procedure that I enumerated above is an exact
copy of a reply to a post in this NG months ago. I copied the formula
because I knew that I will be using them somewhere down the line.

Now, that you need them, I am sharing them with you. I will b
searching the forum for the original author of this procedure and
will personally thank him/her for this contribution. Isn't this NG jus
super

 

[Niek Otten]

Type 61414121022 in A1
In B1:
=(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1))*3+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1)
In C1: =10*A1+ABS(ROUND(B1,-1)-B1)

Check with some other figures not resulting in a 0 check-digit, post again
if not right.

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[kcc]

*THIS FORMULA WILL TAKE CARE OF STEPS 2 AND 3:

=SUMPRODUCT(--(MID(A1,{1,3,5,7,9,11},1))*3

If A1 is entered as a number (vs. text) and UPC codes can start
with 0, I would replace A1 with text(A1,"00000000000") to
force leading 0's to count as digits.

 

[BenjieLop]

If A1 is entered as a number (vs. text) and UPC codes can start
with 0, I would replace A1 with text(A1,"00000000000") to
force leading 0's to count as digits.

A very basic condition has been overlooked, I guess ... the cel
containing the 11-digit number must be in the TEXT format. Thanks fo
pointing it out

 

[Dana DeLouis]

Just an idea. Once you have your sum from step 5, another option for step 6
could be the following without an IF statement.

=MOD(10-MOD(A1,10),10)

 

[Thomas Lutz]

The following is a VBA function that will calculate a UPC-A check
digit.
For more bar code solutions please visit www.taltech.com


Function UPCCheckDigit(msg As String) As String
Dim ll As Long
Dim Check As Long
Dim C As String

ll = Len(msg)
If ll <> 11 Then
MsgBox "please supply 11 digit UPC message"
Exit Function
End If
Check = 0 ' init check digit value
For x = 1 To ll
C = Mid$(msg, x, 1)
Select Case x ' calculate check digit
Case 1, 3, 5, 7, 9, 11
Check = Check + Val(C) * 7
Case 10, 2, 4, 6, 8
Check = Check + Val(C) * 9
End Select
Next
Check = (Check Mod 10) + 48
UPCCheckDigit = Chr$(Check)

End Function