Parse a Cell value using another listing showing criteria
 

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.

Parse a Cell value using another listing showing criteria
 

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.

Parse a Cell value using another listing showing criteria
 

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.

Parse a Cell value using another listing showing criteria
 

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.

Parse a Cell value using another listing showing criteria
 

On Mon, 28 Sep 2009 07:13:02 -0700, rosefest
wrote:

Thank you. Not sure how the Macro works, but it did split up most of the
part numbers in my sheet. The places it did not work were where the part
number was different than the samples I provided. But they are in the
minority.
Thanks again.

Glad to help.

The macro uses the following "rule" to parse the string:

"^([A-Z])(\w\d*)(\D+)(\d\D*)(\d*)"

^ Start at beginning of line

Group 1
[A-Z] A single capital letter

Group 2
\w\d* A single letter or digit followed by zero or more digits

Group 3
\D+ One or more non-digits

Group 4
\d\D* A single digit followed by zero or more non-digits

Group 5
\d* Zero or more digits

This "rule" seemed to match all of your supplied examples. I glad to see it
worked on the majority. If there is only a small minority, it may not be worth
it to tweak it further. (And, of course, I don't know if it is possible). But
if you want to provide some examples, I could take a look later this week.
--ron