On Fri, 14 Mar 2008 11:24:00 -0700, Brennan
wrote:

I have huge sheet with thousands of non-standard lines of text. Some of the
lines have the following line in a cell:

ProjID=MFG-Company-DA-33 ProjType=BBAA Uplift=0 CostType=Time Mgr=Smith

I would like to understand how to write the VBA that will skip the lines
that don't match this format and then Break out the items that do. This is
what I have so far.

some sort of loop statement here

If activecell.value (left(5))="projID" then
' this is where a need the help. I want to move everything after ProjID= to
Type= to the next column then move everything after Type= to Uplift= to a
column 2 columns away and so on for the rest of the line.

else activecell.offset (-1,0)

loop

End if

Thanks for the help. I would use text to columns, but only few of the
cells follow the format listed above. Thanks

You haven't specifically defined your format, and I'm not clear if you want the
label to be extracted also, but I made these assumptions to attract the various
segments:

Each segment to be extracted is preceded by a specific ?????= and followed by a
<space.

The ?????= labels all occur in the order given, and there are five of them in
each relevant line.

If the pattern doesn't match, extract nothing.

That being the case, the macro below will extract the relevant data, from lines
that match the format above:

========================================
Option Explicit
Sub SplitSome()
Dim rg As Range
Dim c As Range
Dim i As Long

'set up the range to process
'can be done in a variety of ways
'below assumes the range is contiguous
'and in one column

Set rg = Selection(1, 1)
Set rg = Range(rg.End(xlUp), rg.End(xlDown))

'set up pattern for regex engine
Dim re As Object, mc As Object
Dim Str As String
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern =
"ProjID=(.*?)\s+ProjType=(.*?)\s+Uplift=(.*)\s+Cos tType=(.*?)\s+Mgr=(.*)\s*"
'check all lines
For Each c In rg
Str = c.Value
'if the pattern doesn't match, will return nothing
If re.test(Str) = True Then
Set mc = re.Execute(Str)
'there must be five segments
For i = 1 To 5
c.Offset(0, i * 2).Value = mc(0).submatches(i - 1)
Next i
End If
Next c

End Sub
=========================================
--ron