Turning a 6 or 7 digit number into a code containing numbers and letters.

Hi, I am new to Excel Forum and to complicated Excel programming. I am having trouble converting manufacturer part numbers into our part number code.

We Currently Code product numbers by using the following formula.

0 = P
1 = A
2 = B
3 = C
4 = E
5 = F
6 = H
7 = K
8 = M
9 = N

The manufacturer part numbers are 6 or 7 digits long. They are converted starting from right to left. The second number is converted to a letter and the fifth number is converted to a letter.

ex 1: 274794 = 2K47N4
ex 2: 1022223 = 10B22B3

A "T" is added to the front of the code and an "S" is added to the back. The completed code looks like this.

ex 1: 274794 = T2K47N4S
ex 2: 1022223 = T10B22B3S

Is there a way for excel to do this for me automatically? I have about 10,000 numbers to convert ...

Any help is appreciated.

-Matthew

Hi mathew

Where is the code entered and where does the result get displayed?

The manufacturer part number is in, column B. The code (result) needs to be displayed in column C.

You guys are awesome!! Thanks for the help. It worked and saved me HOURS of work.

Best regards,
Matthew Cucuzza

Sorry to revive such an old thread. It has been a few years since I asked for help converting our part numbers into codes, now I find myself in a position where I need to be able to turn the codes back into part numbers.

My original questions was:

"We Currently Code product numbers by using the following formula.

0 = P
1 = A
2 = B
3 = C
4 = E
5 = F
6 = H
7 = K
8 = M
9 = N

The manufacturer part numbers are 6 or 7 digits long. They are converted starting from right to left. The second number is converted to a letter and the fifth number is converted to a letter.

ex 1: 274794 = 2K47N4
ex 2: 1022223 = 10B22B3

A "T" is added to the front of the code and an "S" is added to the back. The completed code looks like this.

ex 1: 274794 = T2K47N4S
ex 2: 1022223 = T10B22B3S

Is there a way for excel to do this for me automatically? I have about 10,000 numbers to convert ..."

I used the formulas given above to convert these original part numbers into our codes, but would now like to convert our codes back to original part numbers to generate a specific report.

Would anyone be willing to help out with this?

Any help is very much appreciated.

Thanks again,
Matthew

Great!

Thanks again, I just had to modify it a little to make it work for me. Perfect!

Have a good weekend

Great!

Thanks again, I just had to modify it a little to make it work for me. Perfect!

Have a good weekend