I have two part numbers:
A
1 CR4MD6
2 C418MDX7
I need to parse these out into other cells so they look like as follows:

A B C D
1 C R4 MD 6
2 C 418 MDX 7

On a separate sheet I have a listing of all posible combinations of values
in column A, B, C, D.

I was hoping to use data in second sheet to help parse these part numbers
out. I have some 20k lines that I will have to do again at least 2 more
times. As you can see there is no real trend in the data.

Any help is greatly appreciated.

I'm noticing first character is in A, 3rd column starts with M and 4th is
last digit.

If this is always the case, you can just do =LEFT(A2,1) in column 1,
=mid(A2,2,find("m",A2)-1) in column2,
=mid(A2,find("m",A2),len(A2)-find("m",A2)) in column 3 and =RIGHT(A2,1) in
column 4

Otherwise, you may be able to use LOOKUP(), but would kind of depend on the
combinations there are to pick from...

"rosefest" wrote:

I have two part numbers:
A
1 CR4MD6
2 C418MDX7
I need to parse these out into other cells so they look like as follows:

A B C D
1 C R4 MD 6
2 C 418 MDX 7

On a separate sheet I have a listing of all posible combinations of values
in column A, B, C, D.

I was hoping to use data in second sheet to help parse these part numbers
out. I have some 20k lines that I will have to do again at least 2 more
times. As you can see there is no real trend in the data.

Any help is greatly appreciated.

Unfortunately it is not the case. I chose my examples poorly. Here are 5
part numbers as example:
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

In an separate table I have a list of all column B values as well as one for
Column C. My hope is I can split each part number using the data in the
separate table.
Other Table contents look like the below.
R3
418
614
5432




"Sean Timmons" wrote:

I'm noticing first character is in A, 3rd column starts with M and 4th is
last digit.

If this is always the case, you can just do =LEFT(A2,1) in column 1,
=mid(A2,2,find("m",A2)-1) in column2,
=mid(A2,find("m",A2),len(A2)-find("m",A2)) in column 3 and =RIGHT(A2,1) in
column 4

Otherwise, you may be able to use LOOKUP(), but would kind of depend on the
combinations there are to pick from...

"rosefest" wrote:

I have two part numbers:
A
1 CR4MD6
2 C418MDX7
I need to parse these out into other cells so they look like as follows:

A B C D
1 C R4 MD 6
2 C 418 MDX 7

On a separate sheet I have a listing of all posible combinations of values
in column A, B, C, D.

I was hoping to use data in second sheet to help parse these part numbers
out. I have some 20k lines that I will have to do again at least 2 more
times. As you can see there is no real trend in the data.

Any help is greatly appreciated.

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 two part numbers:
A
1 CR4MD6
2 C418MDX7
I need to parse these out into other cells so they look like as follows:

A B C D
1 C R4 MD 6
2 C 418 MDX 7

On a separate sheet I have a listing of all posible combinations of values
in column A, B, C, D.

I was hoping to use data in second sheet to help parse these part numbers
out. I have some 20k lines that I will have to do again at least 2 more
times. As you can see there is no real trend in the data.

Any help is greatly appreciated.