Link to home
Start Free TrialLog in
Avatar of Robert Berke
Robert BerkeFlag for United States of America

asked on

I wish vba regex supported lookbehind. Can anybody grant my wish?

VBA regex supports look ahead, but not look behind.
As a result, I frequently use .submatches which makes my code wordy and harder to understand.

Has anybody ever figured out a way around this?

The following code demonstrates the problem. I desire to extract every integer from a string, and ignore everything else.

Dim regex As Object, match As Object
Set regex = CreateObject("vbscript.regexp")
regex.Global = True
regex.MultiLine = False

' This is displays exactly what I want, but it leads to a wordy msgbox syntax.

regex.Pattern = "(\b|\D)(\d{7})(?=\b|\D)"
Set match = regex.Execute("a1234567 1234 a2072083")

MsgBox match.Count & "'first=" & match(0).submatches(1) & "'last=" & match(match.Count - 1).submatches(1)


' I wish I could do it this way because the msgbox syntax is cleaner. Unfortunately, it does not work because VBA does not support lookbehind.

regex.Pattern = "(?<=\b|\D)(\d{7})(?=\b|\D)"
Set match = regex.Execute("a1234567a2072083")

MsgBox match.Count & "'first=" & match(0) & "'last=" & match(match.Count - 1)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Berke

ASKER

Negative lookahead does not help, but there is a trivial solution to problem .pattern = \d+

I am closing this question, but will ask a more difficult version next week.
Fabrice Lambert:  your suggestion was not helpful, but I am awarding you points anyhow.

It is not your fault as I did not ask the question properly.  Thanks for you efforts.
Long ago, Experts Exchange made it easy to delete an improperly worded question.  Under the new version, the only easy way to do this is by awarding someone the best answer.

I liked it better  the old way.