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











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks