Split apart list of part numbers into different columns
 

I have a list of part numbers (some 5K) that I need to have broken out using
various criteria. The part numbers look like this:

C614C7010
CR3RHH7010
S418MCK7
N5432HX5P
C418MCX5

And they need to look like this:
A B C D E

C 614 C 7 010
C R3 RHH 7 010
S 418 MCK 7
N 5432 HX 5P
C 418 MCX 5

There is no specific trend to the data that I can use MID/Right/Left for.
Of note, I do have a separate sheet in my document that lists out all of the
combinations of Column B, C and E. Not sure if that is of any help here.

As an example, the Other Table contents look like the below as an example of
Column B

R3
418
614
5432

Any help is appreciated.

Split apart list of part numbers into different columns
 

Rose
Send me your file along with that "separate sheet in my document that
lists out all of the combinations of Column B, C and E." That list is the
key to doing this. My email is . Remove the
"extra" from this address. HTH Otto
"rosefest" wrote in message
...
I have a list of part numbers (some 5K) that I need to have broken out
using
various criteria. The part numbers look like this:

C614C7010
CR3RHH7010
S418MCK7
N5432HX5P
C418MCX5

And they need to look like this:
A B C D E

C 614 C 7 010
C R3 RHH 7 010
S 418 MCK 7
N 5432 HX 5P
C 418 MCX 5

There is no specific trend to the data that I can use MID/Right/Left for.
Of note, I do have a separate sheet in my document that lists out all of
the
combinations of Column B, C and E. Not sure if that is of any help here.

As an example, the Other Table contents look like the below as an example
of
Column B

R3
418
614
5432

Any help is appreciated.

Split apart list of part numbers into different columns
 

OK this has to be set up a certain way and may be going around the barn, but
at least for the examples you provided, it works:

1. I am assuming your part numbers to be in cells A:A5.
2. We are going to look up the part numbers from the lists of separate part
numbers you referenced. Format colums G-K as text. The values MUST be entered
as TEXT (not with the apostrophe in the front), in columns H, I, J, and K
(no headings - you don't need the first column since it is always one
character)
like such: THESE EACH MUST BE SORTED ASCENDING AS WELL
418 C 5 010
5432 HX 5P 010
5433 MCK 7
614 MXX 7
R3 RHH 7

3. In cell B1, enter this: =LEFT(A1,1) and copy down to B5.

4. In cell C1, enter this: =LOOKUP(MID(A1,2,4),$H$1:$H$5) and copy down.

5. In cell D1, enter this:
=LOOKUP(MID(A1,LEN(LOOKUP(MID(A1,2,4),$H$1:$H$5))+ 2,20),$I$1:$I$5) and copy
down.

6. In cell E1, enter this: =LOOKUP(MID(A1,LEN(B1&C1&D1)+1,10),$J$1:$J$5)
and copy down.

7. In cell F1, enter this: =MID(A1,LEN(B1&C1&D1&E1)+1,10) and copy down.

IF you formatted and sorted the part lists correctly, the resulting cells in
columnsB thru F will look like this:


C 614 C 7 010
C R3 RHH 7 010
S 418 MCK 7
N 5432 HX 5P
C 418 MCK 5



"rosefest" wrote:

I have a list of part numbers (some 5K) that I need to have broken out using
various criteria. The part numbers look like this:

C614C7010
CR3RHH7010
S418MCK7
N5432HX5P
C418MCX5

And they need to look like this:
A B C D E

C 614 C 7 010
C R3 RHH 7 010
S 418 MCK 7
N 5432 HX 5P
C 418 MCX 5

There is no specific trend to the data that I can use MID/Right/Left for.
Of note, I do have a separate sheet in my document that lists out all of the
combinations of Column B, C and E. Not sure if that is of any help here.

As an example, the Other Table contents look like the below as an example of
Column B

R3
418
614
5432

Any help is appreciated.

Split apart list of part numbers into different columns
 

On Fri, 25 Sep 2009 07:52:01 -0700, rosefest
wrote:

I have a list of part numbers (some 5K) that I need to have broken out using
various criteria. The part numbers look like this:

C614C7010
CR3RHH7010
S418MCK7
N5432HX5P
C418MCX5

And they need to look like this:
A B C D E

C 614 C 7 010
C R3 RHH 7 010
S 418 MCK 7
N 5432 HX 5P
C 418 MCX 5

There is no specific trend to the data that I can use MID/Right/Left for.
Of note, I do have a separate sheet in my document that lists out all of the
combinations of Column B, C and E. Not sure if that is of any help here.

As an example, the Other Table contents look like the below as an example of
Column B

R3
418
614
5432

Any help is appreciated.

What is the difference between this post and your similar one of yesterday, to
which you received several replies?
--ron