UPC-A Conundrum

[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.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")

 

[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)

 

[J.E. McGimpsey]

Thanks, but my wife thinks I'm enough of a jerk already...

Glad to help.