VBA Regular Expression Methods 3

[MattGreer]

I was wondering if someone could point me to a resource where I could find the syntax of the methods used in a regular expression in VBA. Right now I'm using this code in Excel to find a zip code with a bracket:

Sub MatchString()
Dim RgExp As Object
Dim str As String

    str = "Bloomfield, NJ 07003], 509 [Broad St]"
    Set RgExp = CreateObject("VBScript.RegExp")
    RgExp.Pattern = "\d{5}\]"
    Debug.Print RgExp.Test(str)

End Sub

Thing is, I had to do a lot of searching around at tek-tips to find the syntax for "RgExp.Test". There's plenty of info at Microsoft on building the pattern, but then what you can do with the RgExp object itself is a bit of a mystery to me, and the VBA editor was unable to provide me with any information either.

So far, off the top of my head, I know of:

RgExp.Test(str)
RgExp.Replace(str,"yourstring")

I just downloaded the VBScript documentation from Microsoft and I know there's more information in there. Is that the only place, or is there a nify online list of these regular expression methods?

Thanks!!

Thanks!!


Matt

 

[MattGreer]

*bump*

Thanks!!


Matt

 

[RoyVidar]

Look around - search for instance the VB5/6 forum (forum222). Here's a couple of threads from the Access fora thread705-994984, thread705-894702, thread705-999357. In the last one, there are some links to both other threads (with more links) and links to MS documentation.

A web search on Regular Expressions, should also give some hits. Here are a couple RegExpLib.Com, Regular-Expressions-Info

These explicit MS pages may perhaps also be worth a read:
execute method, replace method and submatches collection

Roy-Vidar

 

[kpetursson]

A quick search of google

http://www.google.ca/search?hl=en&q=vb+regexp+methods&btnG=Search&meta=

revield this link

http://juicystudio.com/tutorial/vb/regexp.asp

Hope this is what you were looking for.



Kevin Petursson

 

[MattGreer]

Thanks guys! Stars all around!

Thanks!!


Matt

 

[KenWright]

This thread in particular uses RegExp for finding UK postcodes and so may be useful for what you are trying to achieve

thread68-890890

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Some examples in text from a post of Harlan Grove's in the MS newsgroups:-

>You can separate the lastname (last word) with the
>SepLastTerm macro at
>

http://www.mvps.org/dmcritchie/excel/join.htm#seplastterm

>You will need one (and only one) empty column to the right.
>
>If you have names like Ron de Bruin you might want to
>first use Ctrl+H and change ' de ' to ' de.' first
>then replace the periods by spaces afterwards.
..

Or if one were sensible, one might use regular expressions for general text parsing. Using the function

Function resubstr(s As String, p As String, Optional n As Long = 0) As String
Dim re As Object, m As Object
Set re = CreateObject("VBScript.RegExp")
re.Pattern = p
re.Global = True
Set m = re.Execute(s)
resubstr = IIf(m.Count > 0, m.Value, "")
End Function

it's possible to extract multiple word last names with

=resubstr(s,"\b([a-z]+ +)*[A-Z]\w*$")

where =resubstr("Jose de la Cruz","\b([a-z]+ +)*[A-Z]\w*$") gives "de la Cruz".
It's relatively simple to ignore generational qualifiers (Sr, Jr, III, IV, etc.)

=resubstr("Louis de Bourbon XVI",
"\b([a-z]+ +)*[A-Z][a-z]*(?=(( +)(Sr\.?|Jr\.?|[IVX][IVX]*))|,|\s*$)") gives "de
Bourbon".

Serious text processing in all modern scripting languages (Perl, Python, Tcl, Ruby, [V]JScript, VBScript) is done with regular expressions. It's easy to take advantage of VBScript's facilities in VBA. Character-by-character processing just isn't as capable.

-------------------------

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[klausnrooster]

Hey MattGreer

There are several free regexp apps to help you build expressions with the specificity / sensitivity balance you need. The Regexp Coach is loaded with features. RegexpBuilder is nifty in it's own way. Sorry I don't have urls here but they're easy to find.

Different sw implement regexp in various ways to various degrees - so you may want the vbscript documention handy. I have not tried it, but you probably could use jscript in the same way you are using vbscript. The WSH runs either, so..... And jscript's implementation may be more 'common'/similar to tools above. (?) Just a guess. I'm not really a programmer, more techuser with generalist orientation.

Of interest: findstr command-line tool that XP includes and Baregrep freeware.

Good luck!!