UPC-A Conundrum

B

Bob Crossley

Hello All-
I am working on a price book in Excel 2003. The price book includes
UPC-A codes.
Our number is 0-51943-XXXXX-X. We have approx. 250 different codes.
I am trying to format column A so that all I have to enter is the last
5 digits and cell formatting will add the 0-51943. Column B will be
the equation to return the code plus the check digit. I am using this
equation to return the full 12 digit code including the check digit;
=A1&MOD(10-MOD(SUMPRODUCT(MID(A1,ROW($1:$11),1)*(1+2*MOD(ROW($1:$11),2))),10),10)

This equation returns the correct check digit ONLY if I enter A1 as
'05194350030 (example number, note single quote).

This works fine IF these is no need to further format. My problem is
the equation returns a number that I cannot format to 0-51943-50030-0,
a normal UPC-A format.
I would be most appreciative if someone would lend a hand or thought.
Thanks, Bob Crossley
 
J

J.E. McGimpsey

one way:

Use Insert/Name/Define:

Name in Workbook: pre
Refers to: =TEXT(51943,"000000")

then enter this:

B1: =TEXT(pre & A1 & MOD(10 - MOD(SUMPRODUCT(MID(pre & A1,
ROW($1:$11),1)*(1+2*MOD(ROW($1:$11),2))),10),10),"0-00000-00000-0")
 
B

Bob Crossley

J.E.- Great! Works PERFECT
Thanks! I work for a Beef Jerky Company, If you are interested, I would be willing to throw some Jerky your way for your help
Thanks, Bo
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top