VBA script to parse string variables in column

Hi Everyone,

I've researched to the point of needing help now. Any help would be appreciated.

I have a column that is called Options with a string of words (a chunk of HTML really). In this column I want to parse through each cell and look for each of the following variables:

Draw Hand: (Right,Left)
Draw Weight: (x)
Draw Length: (in half inches)
Arrow Length: (in half inches)

If any of those variable strings are in the cell, then I want them placed in a separate column in the spreadsheet.

Spreadsheet here at Google Docs:

I would ideally like to have a VBA script that is flexible to add more variable strings as needed. Many thanks for your help!

Kind Regards,
Justin

Well done Jerry! Thank you for helping me with this. Does anyone have any thoughts on how we could automatically clean up the data once its been parsed into the new fields? Would like to continue working in the script Jerry has framed up to this point.

Here's how the data looks after its been parsed:

Draw Hand	Draw Weight	Draw Length	Arrow Length	Arrow Size	String Length
Right = $0.00					
Right Hand = $0.00					
Right Hand = $0.00	60-lbs = $0.00	28.5 inch = $0.00			
Right Hand = $0.00	45-lbs = $0.00				
Right Hand = $0.00					
Right = $0.00					
Right Hand = $0.00	45-lbs = $0.00

For the columns the fields should allow for:
Draw Hand: (Right, Left) - 5 char max
Draw Weight: (60) - 2 char max
Draw Length: (28.5) - 4 char max
Arrow Length: (27.5) - 4 char max
Arrow Size: (3000) - 4 char max
String Length: (104) - 3 char max


Current VBA script:

Sub SplitOptionHTMLText()
'
' Lables the columns for the parse to work below
'
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Draw Hand"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Draw Weight"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Draw Length"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Arrow Length"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Arrow Size"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "String Length"

    
' Help from Jerry Beaucaire   4/19/2010
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("O:O").WrapText = True

'add formulas in all cells
Range("P2:P" & LR).FormulaR1C1 = _
    "=IF(ISNUMBER(SEARCH(R1C,RC15)), MID(RC15,SEARCH(R1C,RC15)+LEN(R1C)+2,SEARCH(""<"",RC15,SEARCH(R1C,RC15)+LEN(R1C)+2)-(SEARCH(R1C,RC15)+12)), """")"
Range("Q2:Q" & LR).FormulaR1C1 = _
    "=IF(ISNUMBER(SEARCH(R1C,RC15)), MID(RC15,SEARCH(R1C,RC15)+LEN(R1C)+2,SEARCH(""<"",RC15,SEARCH(R1C,RC15)+LEN(R1C)+2)-(SEARCH(R1C,RC15)+14)), """")"
Range("R2:R" & LR).FormulaR1C1 = _
    "=IF(ISNUMBER(SEARCH(R1C,RC15)), MID(RC15,SEARCH(R1C,RC15)+LEN(R1C)+2,SEARCH(""<"",RC15,SEARCH(R1C,RC15)+LEN(R1C)+2)-(SEARCH(R1C,RC15)+14)), """")"
Range("S2:S" & LR).FormulaR1C1 = _
    "=IF(ISNUMBER(SEARCH(R1C,RC15)), MID(RC15,SEARCH(R1C,RC15)+LEN(R1C)+2,SEARCH(""<"",RC15,SEARCH(R1C,RC15)+LEN(R1C)+2)-(SEARCH(R1C,RC15)+14)), """")"
Range("T2:T" & LR).FormulaR1C1 = _
    "=IF(ISNUMBER(SEARCH(R1C,RC15)), MID(RC15,SEARCH(R1C,RC15)+LEN(R1C)+2,SEARCH(""<"",RC15,SEARCH(R1C,RC15)+LEN(R1C)+2)-(SEARCH(R1C,RC15)+14)), """")"
Range("U2:U" & LR).FormulaR1C1 = _
    "=IF(ISNUMBER(SEARCH(R1C,RC15)), MID(RC15,SEARCH(R1C,RC15)+LEN(R1C)+2,SEARCH(""<"",RC15,SEARCH(R1C,RC15)+LEN(R1C)+2)-(SEARCH(R1C,RC15)+14)), """")"
            
    
Range("P2:U" & LR).Value = Range("P2:U" & LR).Value     'removes the formulas
Range("P:U").Columns.AutoFit                            'tidies up appearance

End Sub

Thank you,
Justin