Creating UPC Codes...
 

Hey people, first timer here. Didn't exactly know where to put this, so
I followed the big red typing...

Anyway, currently, we use excel to create UPC codes for our products.
I was wondering how to make it more streamlined, and possibly
automatic...

Let me explain how it works. Columns A-F are six seperate fixed
numbers that correspond to our six digit vendor code. Then, in cells
G-K, we have a 5 digit number, starting at 30001 that gets one bigger
for each UPC (30001, 30002, 30003, etc).

There are several more issues I need to address later about making this
UPC code generator automatic, but for now, I would like to know how I
can make excel generate consecutive numbers for the last 5 digits.
Each digit must be in a seperate column, and it has to create a 5 digit
number (30001, 30002, 30003, etc).

Thanks for your help, if you can answer this one, I will give you a
more challenging one later...:rolleyes:

KillerTofu


--
killertofu
------------------------------------------------------------------------
killertofu's Profile: http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=506922

Creating UPC Codes...
 

I think this might work for you:

Manually enter the number in cells G1:K1. Then, enter the following
formulas in cells G2:K2. Copy down as many rows as you need.

G2 = MID(CONCATENATE(G1,H1,I1,J1,K1)+1,1,1)
H2 = MID(CONCATENATE(G1,H1,I1,J1,K1)+1,2,1)
I2 = MID(CONCATENATE(G1,H1,I1,J1,K1)+1,3,1)
J2 = MID(CONCATENATE(G1,H1,I1,J1,K1)+1,4,1)
K2 = MID(CONCATENATE(G1,H1,I1,J1,K1)+1,5,1)

Note: This will give the results as text. If you need the results to still
be numbers, then just put the above formulas within a VALUE() function.

=VALUE(MID(CONCATENATE(G1,H1,I1,J1,K1)+1,1,1))

HTH,
Elkar

"killertofu" wrote:


Hey people, first timer here. Didn't exactly know where to put this, so
I followed the big red typing...

Anyway, currently, we use excel to create UPC codes for our products.
I was wondering how to make it more streamlined, and possibly
automatic...

Let me explain how it works. Columns A-F are six seperate fixed
numbers that correspond to our six digit vendor code. Then, in cells
G-K, we have a 5 digit number, starting at 30001 that gets one bigger
for each UPC (30001, 30002, 30003, etc).

There are several more issues I need to address later about making this
UPC code generator automatic, but for now, I would like to know how I
can make excel generate consecutive numbers for the last 5 digits.
Each digit must be in a seperate column, and it has to create a 5 digit
number (30001, 30002, 30003, etc).

Thanks for your help, if you can answer this one, I will give you a
more challenging one later...:rolleyes:

KillerTofu


--
killertofu
------------------------------------------------------------------------
killertofu's Profile: http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=506922

Creating UPC Codes...
 

Winner, winner, chicken dinner...

Thanks homes, you're a genius. You shoulda seen my previous formulas
that were in there. IF within IF within IF. It was messy.

Speaking of concatination, if you have time, do you think you could
explain what the function is actually doing? I'm one of those people
who has to know why it's working.


--
killertofu
------------------------------------------------------------------------
killertofu's Profile: http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=506922