Return text with regex find function

In VBA , I'm trying to return the result of a regex find to my own sub with the following function. The problem is, I can't figure out what it's returning, or how to get it to return the actual text of the match. I also need to test if there is or isn't a match.

The function, from this page ( http://www.tmehta.com/regexp/add_code.htm ) is reproduced here for convenience:

Please Login or Register  to view this content.

This function seems to allow for the possibility of finding more than one match at a time, which is not what I want.

Here is an example of how I'm trying to use it. TwoWaySplitNum is a string variable.

Please Login or Register  to view this content.

There's also a RegExpSubstitute function on that page if you're interested.

Hello jrussell,

Is this a question or a comment? You can replace substrings with RegExp. You need to provide the data you want parsed. RegExp are rather complex and the more data you provide the better.

Here are some examples of data I need to parse. It's percentage splits among salespeople that I need to find. See the attached workbook for more in-depth examples. If the 'desired result' columns error out on your computer, see the 'desired result text' columns.

April Totowa/Adam Zeller 50/50
75/25 Robin Heller/ Matthew Krason
Mike Viera/ Mike Stein 50/50 eff: 08/01/09 E&M
Abe Smith/John Franks/Lisa Lowe 60/20/20

It works fine if I just enter the formulas on a worksheet. I'm just having trouble using the RegExpFind function in a macro.

I have the logic worked out to first look for a three-way split, and if that fails, to look for a two-way split, and I can work out how to match the patterns with regex. I don't need help with that.

I need help figuring out in VBA how to return actual matching text if there is any, and how to handle it if there isn't any.

Here is the half-written macro where I'm trying to use this. If you copy this and the RegExpFind into a module and run it on the attached file "real sheet for upload" you'll be able to step through and follow along in the locals window. Hope this helps.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Edit: Got past that step, still working on it though...