VBA script to parse string variables in column

[jlay]

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

[JBeaucaire]

I like to use normal worksheet formulas to do the brunt of the work, so you can run this macro without the last two lines of code to see the formulas that are created. It might work as is if you simply add another column and copy the formula from column F over...

Please Login or Register  to view this content.

[jlay]

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Thank you,
Justin

[JBeaucaire]

Here you go:

Please Login or Register  to view this content.