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
3
Medium Priority
?
571 Views
Last Modified: 2012-05-07
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

Open in new window

0
Comment
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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
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

by:trayceejay
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

by:zorvek (Kevin Jones)
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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‚Ķ

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question