Regular Expression Pattern All Letters In String

Hi all,

Using Excel 2013.

Trying to get the pattern to return all letters in a string
My test string is "asdf134!#@$adsf]"
My current pattern is "[a-zA-Z]*"
So the result should be "asdfadsf"
But my pattern stops when 1 is encountered so the output is "asdf"

How can I get the pattern to continue through the entire string?

thx
w

Sub:

Sub foo()

    Dim t As String
    Dim s As String
    
    s = "asdf134!#@$adsf]"
    t = TestRE(s)

    Debug.Print t

End Sub

Function

Function TestRE(s As String) As String
    
    Dim regex As New RegExp
    Dim colregmatch As MatchCollection

    With regex
       .Pattern = "[a-zA-Z]*"
       .MultiLine = False
       .Global = True
       .IgnoreCase = False
    End With

    Set colregmatch = regex.Execute(s)
    
    TestRE = colregmatch(0)
End Function

Hi goss,
try it

Sub foo()
Dim t As String, s As String
s = "asdf134!#@$ad654sU]"
t = TestRE(s)
MsgBox t
End Sub

Function TestRE(s As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^a-z]"
    .Global = True
    .IgnoreCase = True
    TestRE = .Replace(s, "")
End With
End Function

(I forgot the name link to the Regexp in the available references)

How about ...

Function TestRE(s As String) As String
    With New RegExp
       .Pattern = "[^A-Z]"
       .Global = True
       .IgnoreCase = True
        TestRE = .Replace(s, "")
    End With
End Function

Nilem / Shg,

Thanks.
Nice use of recursion - the function works great!

Do you know is there a class for letters only.
I've Google'd quite a bit haven't found anything.

thx
w

There's no recursion in those functions.

"[A-Za-z]" is the pattern for letters, or just "[A-Z]" if ignoring case.

I would combine nilems's late-binding approach (to avoid the need for a reference) with a static object variable for speed:

Function TestRE(s As String) As String
    Static oRE      As Object

    If oRE Is Nothing Then
        Set oRE = CreateObject("VBScript.RegExp")
        oRE.Pattern = "[^A-Z]"
        oRE.Global = True
        oRE.IgnoreCase = True
    End If

    TestRE = oRE.Replace(s, "")
End Function

Thanks shg,

Now I understand.
I was trying to extract only letters
You are replacing if not a letter with nothing.

Genius, thanks!
w

You're welcome.