Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# UPC-A codes check digit macro request

Posted on 2009-05-18
Medium Priority
571 Views
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
Question by:trayceejay
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 24412861
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 Comment

ID: 24413001
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

LVL 81

Expert Comment

ID: 24413033
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month7 days, 22 hours left to enroll