new to regular expression UDF

[mcranmoss]

I am new to regular expressons. This first example of a regular expression does work as intended

Function onlynumbers(ByVal ref As String)
Dim rx As Object
Set rx = CreateObject("VBScript.RegExp")
With rx
    .Pattern = "\D"
    .Global = True
    onlynumbers = .Replace(ref, " ")
End With
End Function

But this one is supposed to reduce multiple spaces to one space, and doesn't work:

Function test(ByVal ref As String)
Dim rx As Object
Set rx = CreateObject("VBScript.RegExp")
With rx
.Pattern = "(\S+)\x20{2,}(?=\S+)"
.Global = True
End With
End Function

I've clearly not grasped the structure of this. All advice welcome.

Mark

 

[pgc01]

Hi Mark

The second function:

- executes no action
- returns nothing

To reduce multiple spaces to one space, try:

Function TrimSpaces(ByVal s As String) As String
Dim rx As Object
 
Set rx = CreateObject("VBScript.RegExp")
With rx
    .Pattern = "\s+"
    .Global = True
    TrimSpaces = .Replace(s, " ")
End With
End Function

 

[mcranmoss]

Thats great! thanks.

I struggling to customize this, so clearly haven't fully understood the syntax etc. If someone has ample time could they tackle the following questions for me?

So in the 'retain only numerals' example the .Pattern is set to \D (everything that's not a digit) so 'ref' in .Replace(ref, "/") refers to the pattern selected, and replaces it.

What is the syntax for adding multiple options and combinations to the Pattern property? \D and \s

In order to help me grasp this could someone configure this to extract 4 digits from a given string?

Function fourdig(strData As String) As String
...
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "[0-9][0-9][0-9][0-9]"...
End Function

many thanks,

Mark

 

[pgc01]

You can match the pattern and get the value from the matches collection.

Function fourdig(strData As String) As String
Dim rx As Object
Dim rxMatches As Object
 
    Set rx = CreateObject("vbscript.regexp")
    With rx
        .Global = False
        .Pattern = "\d{4}"
 
        Set rxMatches = .Execute(strData)
        fourdig = rxMatches(0)
    End With
End Function

Remark: maybe you should start by reading an introduction to regular expressions, and then study some examples. If you google regular expressions you'll find millions of matches. You can start for ex. here:

http://www.aivosto.com/vbtips/regex.html


Also in my previous code a better match would be "\s{2,}" as you only need to do the replacement if there are at least 2 contigous spaces.

 

[mcranmoss]

That's great.

Your suggestion about studying is very sound. I have been baffled by the online examples I have so far encountered. Your examples have been a great help!

Mark

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[brettdj]

This Using Regular Expressions in Visual Basic for Applications and Visual Basic 6 article (outside the EE paywall) by Patrick Matthews is also an excellent reference