generating part numbers

[R_Brown]

I wrote a simple spreadsheet that we enter all of our product we
purchase in, and it calculates the reselling price very nicely. Over
time this has grown to have a few thousand part numbers. Now we are
going to produce our own catalog, and I would like to change the
supplier part number, into a unique number to publish in our catalog.
Doing this individually would take forever, so I was hoping to make a
simple formula to automate it. By multiplying the supplier part number
by 1.05 for example would work, but the problem is that many of the
manufacturers part numbers have a letter in it. For example, a part
number may be 700234w. I do not know of a formula that would work for
a value containing a letter... Can someone point me in the right
direction to accomplish this? RBrown

 

[Norman Harker]

Hi RBrown!

If you want to retain the letter:

=ROUND(LEFT(A1,LEN(A1)-1)*1.05,0)&RIGHT(A1,1)

If you want to drop the letter:

=ROUND(LEFT(A1,LEN(A1)-1)*1.05,0)

I've assumed that you don't want decimal part numbers

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ken Wright]

Why not just append an index number to the supplier numbers, eg

000001 & 435fsd53f35
000002 & 432sdf4se5
000003 & 2as4d5asf5
000004 & as57df5as5
000005 & 437dsaf7a5

If your sheet starts on row 1, then assuming your data starts in Col B, in ColA
in A1 put

=TEXT(ROW(),"00000")&B1 and copy down.

When done you can simply copy ColA and paste special as values, and then A
becomes your part numbers.

 

[R_Brown]

Thank you Norman & Ken for the terrific (and quick!)options. Ken,
want to conceal the original part number, so Norman's options work bes
for me... but they are both terrific!!!

May I ask for 3 modifications? Some of the supplier’s part numbers ar
only 3 digits. For example, when using the second code, (the one whic
drops the letter) with the part number of "140", "141", and "142", th
calculated new part number comes up the same for all three at "15".
realize this must be something to do with rounding the numbers up (yo
are right, I do not want decimals), can you suggest a change so th
numbers are not the same?

Keeping the above modification in mind, to give a consistent look t
the catalog, would it be possible to have the final part number be
digits long? I realize this may be impossible, and it really is no bi
deal if it cannot be done. Even it the number started with zeros, tha
would be fine.

Therefore instead of looking like:
436653
543352
15

It would be:
00436653
00543352
00000015

Just looks a little cleaner.

And finally, if possible I would like to add "PN" to the beginning o
the new part numbers.

Again, I am humbled by your knowledge on the topic, and am trying har
to learn, Thank you all, RBrown

 

[Norman Harker]

Hi RBrown!

This looks like it covers the three additional specifications:

=IF(ISNUMBER(--RIGHT(A1,1)),ROUND(A1*1.05,0)*10^(8-LEN(A1)),ROUND(LEFT
(A1,LEN(A1)-1)*1.05,0)*10^(9-LEN(A1)))
The IF function treats codes with a trailing letter differently from
those without it.
Multiplying by 10 raised to a power based on length of the base code
secures an 8 digit code.

But you might add a 4th specification that 140z and 140y should
produce different product codes:

=IF(ISNUMBER(--RIGHT(A20,1)),ROUND(A20*1.05,0)*10^(8-LEN(A20)),ROUND(L
EFT(A20,LEN(A20)-1)*(1.05+CODE(RIGHT(A20,1))/100),0)*10^(9-LEN(A20)))
I've used CODE to add to the multiplier a different amount based upon
the trailing letter

I think that this should be OK but you'll need to test carefully and
especially test that you don't get two codes the same.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.