Hello jrussell,
Sorry for the delay. I had some other unexpected business to attend to today. Here is the macro. It will size the range automatically and put the parsed data into the cells to the right of the raw (unparsed) data. This example starts at "A2" on "Sheet1". The parsed data is placed in columns "B:C". You can change this for your needs.
'Written: April 23, 2010
'Author: Leith Ross
Sub SplitData()
Dim Cell As Range
Dim RegExp As Object
Dim Rng As Range
Dim RngEnd As Range
Dim S1 As String, S2 As String
Dim SalesPeople As String
Dim SplitRatio As String
Dim T1 As Boolean, T2 As Boolean
Dim Wks As Worksheet
Set Wks = Worksheets("Sheet1")
'Data to parse starts in "A2"
Set Rng = Wks.Range("A2")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Global = False
RegExp.IgnoreCase = True
SalesPeople = "^[a-zA-Z\/\s]+\s|\s[a-zA-Z\/\s]+\s|\s[a-zA-Z\/\s]+$"
SplitRatio = "^[\/\d]+\s|\s[\/\d]+\s|\s[\/\d]+$"
For Each Cell In Rng
RegExp.Pattern = SalesPeople
T1 = RegExp.Test(Cell)
If T1 Then S1 = RegExp.Execute(Cell)(0)
RegExp.Pattern = SplitRatio
T2 = RegExp.Test(Cell)
If T2 Then S2 = RegExp.Execute(Cell)(0)
If T1 And T2 Then
Cell.Offset(0, 1) = S1 'Sales people in column "B"
Cell.Offset(0, 2) = S2 'Split info In column "C"
End If
Next Cell
Set RegExp = Nothing
End Sub
Bookmarks