Use VBA and REGEX to Parse String

**JohnM3** · 11-15-2011, 08:12 PM

Hi

I have variations of the following type of string generated from a stat program:

Equation: (On 1 Row)
-------------------------------
DLOG(DEM_B_C_TRP(-3)/1000+1)*.01/(.01+.24947)+DLOG(DEM_B_C_VT(-1)/1000+1)*.35947/(.01+.24947)

I would like to pass a regex PATTERN to a VBA function such that the resultant
output would look like this:

(This output results in 3 rows)

DLOG(DEM_B_C_TRP(-3)/1000+1)*.01/(.01+.24947)

+

DLOG(DEM_B_C_VT(-1)/1000+1)*.35947/(.01+.24947)

==========================================
Whats important to *see* within the equation
is that there are

+ signs WITHIN () and
+ signs NOT within ()

and it is the + signs not within () that are the discrimenators
that identify each _segment_ of the overall equation.

==========================================
At present I am using the function below (found at a MS site)
but I am not certain it is the correct function to use.

More importantly, I just cannot figure out the correct pattern
to employ that deals with definitions above.

I have tried numerious patterns and my current one is

=msfind(A22,"(DLOG)+[^Z.*]+.*")

Bottom line - I just can't *see* the correct syntax. (This is the first
time I have tried RegEx)

Current Function:

Note: I realize the current function could NOT return multiple
rows but at this time I am trying to get it to just return
the 1st segment

Please Login or Register  to view this content.

regards
John

**Paul** · 11-15-2011, 11:23 PM

Hi John,

I don't use RegEx patterns (often or at all), but if it is always two DLOG expressions separated by a +, then you could use LEFT and MID functions to get your results (either formulas or VBA).

Please Login or Register  to view this content.

**Leith Ross** · 11-16-2011, 10:37 PM

Hello John,

Sometimes it not possible to use a single pattern to extract information. This true for what you want to accomplish. This macro uses a divide and conquer approach. The text is parsed into single strings whose data is in parentheses.Each group is parsed again into a string that is not enclosed in the parentheses. If there is any data not in parentheses then it is searched for a plus sign. If the plus sign exists then that parsed group is copied to the worksheet and the destination address is incremented.

The second macro demonstrates how to use the parsing macro. It would easy to create a loop to step through a range of equations. Each equation could then be parsed and copied to the worksheet.

Parsing Macro Using Regular Expressions

Please Login or Register  to view this content.

Calling and Using the Parsing Macro

Please Login or Register  to view this content.

**JohnM3** · 11-17-2011, 03:56 PM

Hi Paul

It is very important to -me- that you know that I responded to your post yesterday and APPRECIATE
your taking the time to respond to me.

At this time I have "had it" with my service provider (pinging google gets 1 receipt and 3 failures) and am moving over to DSL (which I thought I would never do)

I just don't know why my response to you did not take.

Your solution - as I presented the problem - is fine - thanks - but my original post _understated_
the complexity and number of terms involved. And I suspect - but don't know for certain -
that the right way to go on this is regex. The number of equations involved can go as deep
as 150 and each equation may contain 1 to 15 terms. The text part of each term can change
even within the same model - because thats what the modelers do.

I am on the tail-end of the process and told to just "deal with it" -g-

Thanks again for your response.

regards
John

**Leith Ross** · 11-17-2011, 04:04 PM

Hello John,

Did you try the solution I posted?

**JohnM3** · 11-17-2011, 04:04 PM

Hi Leith

In all honesty I was "hoping" that you would respond to this problem as I am an avid
reader of your posts - even with respect to problems you resolve - and which I
don't have a current need for.

At this time - today- I am "heads-down" and have to do mickey-mouse stuff(s) that are time
killers. I hope to try and look forward to trying your solution on Saturday.

To beat this to death - I very much appreciate your efforts with respect to this problem.

regards
John

**Leith Ross** · 11-17-2011, 04:13 PM

Hello John,

If you need help merging the code with your own, let me know and I will help you out.

**JohnM3** · 11-19-2011, 03:50 PM

Hi Leith

I did not run your code successfully and good chance the fault is mine.

What follows is in two parts - both put in code blocks - for I hope an easier read.

Part 1 is the individual variable values as I walked thru the code.

Part 2 is the actual subroutines I ran that produced Part 1. These routines are only
slightly different than your code posting and I dimmed Subtext as string due to option e
error.

Part 1 - this is not real code but a walking thru the code ....
I don't know how this will look in a code black.

Please Login or Register  to view this content.

Part 2

The calling and called routines.

Please Login or Register  to view this content.

Again - I am very gun shy that I introduced an error that I am not seeing.
And this in spite of my 4/5 hr effort to understand.

I think what you were going for and what is exactly needed is .....

Term1 + Term2 + Term3 + ..... TermN

FWIW

regards
John

**Leith Ross** · 11-19-2011, 08:25 PM

Hello John,

I am not sure what problems you are experiencing with the code. Attached is an example workbook using the parsing macro. This will display 3 equations on Sheet1.

If you need assistance incorporating this macro into your code then you should post your workbook. It will be faster than posting a lot of questions and answers.

**JohnM3** · 11-19-2011, 11:38 PM

Hi Leith

Your code is perfect. As in - exactly what is needed.

I don't know what error or error(s) I introduced but it sure was a waste of your time
and mine. Sorry.

I need to goto Amazon and find some RegEx books to get a better handle
on the pattern thingie.

Thank you very much for your solution. It is real world and comes into play
more than you might imagine.

Note:
If you were curious the equations are/were being derived from a stat thingie
called EViews.

After closing this post I will put SOLVED in the header (I have to remember how -g-)

Again, thank you.

regards
John