Regular expression pattern

[danoneill]

Can someone please advise what the correct pattern is to return the text between two search strings?

For example, in the html string below, I want to return the text between "selected>" and "<". In this example I want "NA".

"<SELECT class=nfinput name=pace0><OPTION value=L>L</OPTION><OPTION value=P>P</OPTION><OPTION value=OP>OP</OPTION><OPTION value=M>M</OPTION><OPTION value=OM>OM</OPTION><OPTION value=BK>BK</OPTION><OPTION value=NA selected>NA</OPTION><OPTION value=SCR>SCR</OPTION></SELECT>"

I've tried using the lookahead/lookbehind;
(?<=selected>)(.*?)(?=<)
but it returns a syntax error in regular expression (5017).

I used Tushar Mehta's test website (http://www.tmehta.com/regexp/regexpfind.asp) as well as in VBA / Excel 2002 and get the same error.

 

[cgcamal]

Hi danoneill,

You can try with the following to isolate part you want you can do:

1-) Search and replace with nothing this Regex

.*selected>

2-) Search and replace with nothing this Regex

<.*

3-) you'll get the text you want

Hope this helps,

Regards

 

[pgc01]

Hi

Try:

Function GetSelected(s As String) As String
 
With CreateObject("VBSCript.RegExp")
    .Pattern = ".*selected>([^<]+)<.*"
    GetSelected = .Replace(s, "$1")
End With
End Function

 

[danoneill]

Thanks pgc01, that works.

Would you mind explaining the pattern and the .Replace?

Dan

 

[pgc01]

Hi Dan

I - the solution posted

The pattern translates to:

1 - any number of any characters

followed by

2 - selected>

followed by

3 - a string of characters not equal to the character "<"

followed by

4 - the character "<"

followed by

5 - any number of any characters

This pattern reproduces your string. It includes all the characters of the string.

I enclosed part 3 in parentheses. This means that I'm capturing this part into a submatch, in this case it's the first submatch.

I then use the .Replace() method of the RegExp object to replace the whole string by the first submatch and that's what the function returns.

Notice that the pattern assumes that the sequence "selected>" + "some characters" +"<" exists. If that's not the case the function returns the whole string. You may want to tweak the pattern so that if the sequence does not exist, then you return an empty string, or else test first if the there's a pattern match and only then use the .Replace().


II - Another solution

As usual there would be other approaches possible. For ex. in this next one I'm focusing only on the part "selected>" + "some characters" +"<". If this part exists I return the value of the first submatch.

Function GetSelected(s As String) As String
 
With CreateObject("VBSCript.RegExp")
    .Pattern = "selected>([^<]+)<"
    If .Test(s) Then GetSelected = .Execute(s)(0).submatches(0)
End With
End Function

Remark: in your post you say you tried a solution using a LookBehind. LookBehind is not yet implemented in the RegExp object you use in vba.

HTH

 

[danoneill]

Thanks again. I appreciate the time you've taken to explain it all.

 

[pgc01]

I'm glad it helped. Cheers!