Split apart list of part numbers into different columns

[rosefest]

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.

 

[Otto Moehrbach]

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 (e-mail address removed). Remove the
"extra" from this address. HTH Otto

 

[cm]

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

 

[Ron Rosenfeld]

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