• Status: Solved
• Priority: Medium
• Security: Public
• Views: 589

# UPC-A codes check digit macro request

I have a listing of 11-digit codes that I need to create into UPC-A codes to store in Excel, the barcode is not required. Can someone create a macro with install directions?

I have the UPC Code in one column and want to get the UPC-A (with check digit code) in the column next to it
``````UPC CODE	UPC-A Code
69681200001
69681200002
69681200003
69681200004
69681200005
69681200006
69681200007
69681200008
69681200009
69681200010
69681200011
69681200012
69681200013
69681200014
69681200015
69681200016
69681200017
``````
0
Tracy Johnson
• 2
1 Solution

ConsultantCommented:
You can use a formula. Assuming the UPC code is in A1:

=9-MOD(SUMPRODUCT(VALUE(MID(A1,ROW(OFFSET(\$A\$1,0,0,LEN(A1))),1))*(MOD(ROW(OFFSET(\$A\$1,17-LEN(A1),0,LEN(A1))),2)*2+1))-1,10)

Kevin
0

Author Commented:
Thanks for the quick response! I have a question on how to handle if it's not in A1.

If it's not in A1, do I simply change all A1 to be the cell it's in? Does this include \$A\$1 or no?

0

ConsultantCommented:
No. Do not change the \$A\$1 references. That is used as a trick to make the SUMPRODUCT function work. The same formula assuming the value is in A2:

=9-MOD(SUMPRODUCT(VALUE(MID(A2,ROW(OFFSET(\$A\$1,0,0,LEN(A2))),1))*(MOD(ROW(OFFSET(\$A\$1,17-LEN(A2),0,LEN(A2))),2)*2+1))-1,10)

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.