Parse a Cell value using another listing showing criteria

[rosefest]

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.

 

[Sean Timmons]

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]

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

 

[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]

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

Given your examples, I would still wonder if there is a pattern. The following
VBA Macro seems to parse the part numbers as you require. But, of course, if
there are other patterns, it may not work.

The macro first clears the five columns adjacent to the original part numbers,
and then formats them as text. So don't test this on original data.

I'd be interested in part numbers that this routine fails on, if it's not too
much trouble.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the original part numbers. Then <alt-F8>
opens the macro dialog box. Select the macro by name, and <RUN>.

================================
Option Explicit
Sub ParsePartNum()
Dim c As Range, rg As Range
Dim s As String
Dim i As Long
Dim re As Object, mc As Object
Set rg = Selection
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^([A-Z])(\w\d*)(\D+)(\d\D*)(\d*)"

With Range(rg.Offset(0, 1), rg.Offset(0, 5))
.Clear
.NumberFormat = "@"
End With

For Each c In rg
s = c.Value
If re.test(s) = True Then
Set mc = re.Execute(s)
For i = 0 To mc(0).submatches.Count - 1
c.Offset(0, i + 1).Value = mc(0).submatches(i)
Next i
End If
Next c

Set re = Nothing
End Sub
===============================
--ron

 

[rosefest]

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.

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

Given your examples, I would still wonder if there is a pattern. The following
VBA Macro seems to parse the part numbers as you require. But, of course, if
there are other patterns, it may not work.

The macro first clears the five columns adjacent to the original part numbers,
and then formats them as text. So don't test this on original data.

I'd be interested in part numbers that this routine fails on, if it's not too
much trouble.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the original part numbers. Then <alt-F8>
opens the macro dialog box. Select the macro by name, and <RUN>.

================================
Option Explicit
Sub ParsePartNum()
Dim c As Range, rg As Range
Dim s As String
Dim i As Long
Dim re As Object, mc As Object
Set rg = Selection
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^([A-Z])(\w\d*)(\D+)(\d\D*)(\d*)"

With Range(rg.Offset(0, 1), rg.Offset(0, 5))
.Clear
.NumberFormat = "@"
End With

For Each c In rg
s = c.Value
If re.test(s) = True Then
Set mc = re.Execute(s)
For i = 0 To mc(0).submatches.Count - 1
c.Offset(0, i + 1).Value = mc(0).submatches(i)
Next i
End If
Next c

Set re = Nothing
End Sub
===============================
--ron

 

[Ron Rosenfeld]

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