VBA to split contents of cell
 

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

VBA to split contents of cell
 

Use Autofilter to check the left side of the string. It should be
faster than looping through all the cells.

PB

VBA to split contents of cell
 

PB,

Thanks for the reply. I'm trying to automate this so I change the value of
those cells and leave the cells that do not fit the format are not changed.
If I autofilter and then do the text to columns, it would still affect the
non conforming cells. Any advise? Thanks in advance.

B

VBA to split contents of cell
 

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

VBA to split contents of cell
 

Outstanding - thank you. Very helpful!

b

VBA to split contents of cell
 

"Brennan" wrote in message
...
Outstanding - thank you. Very helpful!

I am constantly amazed at the knowledge of the people here. I consider
myself to be an adept intermediate Excel user - but sometimes I don't even
understand the questions asked here never mind the solutions :-)

VBA to split contents of cell
 

On Fri, 14 Mar 2008 12:47:03 -0700, Brennan
wrote:

Outstanding - thank you. Very helpful!

b

Glad to help. Let me know if it requires any "tweaking".
--ron

VBA to split contents of cell
 

On Fri, 14 Mar 2008 20:07:57 GMT, "gavin"
wrote:


"Brennan" wrote in message
...
Outstanding - thank you. Very helpful!

I am constantly amazed at the knowledge of the people here. I consider
myself to be an adept intermediate Excel user - but sometimes I don't even
understand the questions asked here never mind the solutions :-)


Me too.

And sometimes it can be very tough to figure out the question.
--ron