vba help - Count only Alphabets from a string using Regular expression

[malleshg24]

Hi Team,

Using Regular expression Count only alphabets letters. Both small and Capital only.

Dim str as string
str = "464AH C6pYX"


expected Count ---- 6 alphabets ie (AHCpYX)



Thanks
mg

[snb]

See:

https://www.w3schools.com/jsref/jsref_obj_regexp.asp

[malleshg24]

Hi Snb,

Thanks for your help , Modified code giving me correct output.


any other regular expression possible here.
If TextLineRegExpValidation(s, "[a-z]") Then



Sub test()
Dim s As String


s = "A SD FGFD asfasjas 564564.0000 454.000 456.00"

If TextLineRegExpValidation(s, "[a-z]") Then
MsgBox "found"

Else

MsgBox "No Found"
End If

End Sub




Function TextLineRegExpValidation(ByVal textLine As String, ByVal regexPattern As String) As Boolean


Dim RegExp As New RegExp
Dim String_CountRegExp As Long
'Set RegExp = CreateObject("VBScript.RegExp")

With RegExp
.Pattern = regexPattern
.Global = True
.IgnoreCase = True
.MultiLine = True
End With

TextLineRegExpValidation = RegExp.test(textLine)



Dim Matches As MatchCollection
Set Matches = RegExp.Execute(textLine)
'Return the corrected count of matches
String_CountRegExp = Matches.Count



Set RegExp = Nothing


End Function




Thanks
mg

[SamT]

Send each Character to the function and count the times the function returns true.

Use
For i = 1 to Len(s)
and
Abs(CLng(Function call))
and
Mid(s,i,1)

Abs converts - to +
CLng converts True to -1

[Paul_Hossler]

malleshg24 --



Why did you need to use RegEx?

[SamT]

I'm guessing class assignment