CODEWITHSUNDEEP
regexvba
Jeanno
Jeanno

Reputation: 2859

Regex for Matching A Whole Word Containing Special Characters in VBA

I am trying to use Regex in VBA to match a whole word containing a hyphen and numbers. I know the \b would not work because it only set alphabetic boundaries.

Can this be done in Regex in VBA?

rx.Pattern = "[a-z][a-z][0-9]-[0-9][0-9]"

EDIT: I am sorry if I wasn't clear enough. My pattern has the following format "AA2-11". I want to match that whole string, that is why I can't use rx.pattern = "[a-z][a-z][0-9]-[0-9][0-9]" because that will hit a match if you have for example "AA2-11-4", while I just want "AA2-11"

Upvotes: 1

Views: 2380

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Is this what you want?

MORE FOLLOWUP

Thanks for updating your answer, however I am afraid that it doesn't work. Let's say my document has for example AA12-12,AB14-26. The first occurence is not matched. 

Sub Sample()
    Dim regString As String
    Dim myRegExp As RegExp

    regString = "AA12-12#AB14-26"                     '<~~ Matches
    'regString = "#AA12-12,AB14-26#"                  '<~~ Matches
    'regString = "AA2-11 is a sample string"          '<~~ Matches

    'regString = "This is a sample AA2-11-11"         '<~~ Doesn't Match
    'regString = "This is a sample AA2-11-11 string"  '<~~ Doesn't Match
    'regString = "This is a sample AA2-11-11 string"  '<~~ Doesn't Match

    regString = " " & regString & " "

    Set myRegExp = New RegExp

    With myRegExp
        .Global = True
        .Pattern = "\b[a-zA-Z]{2}\d{1,}-\d{2,}\b(?=[^-])"
        If myRegExp.Test(regString) Then
            Debug.Print "Found"
        Else
            Debug.Print "Not Found"
        End If
    End With
End Sub

Or like this

Sub Sample()
    Dim regString As String
    Dim myRegExp As RegExp
    Dim myMatches As MatchCollection
    Dim myMatch As Match

    regString = "AA12-12,AB14-26"             '<~~ Matches
    'regString = "#AA12-12,AB14-26#"     '<~~ Matches
    'regString = "AA2-11 is a sample string"          '<~~ Matches

    'regString = "This is a sample AA2-11-11"         '<~~ Doesn't Match
    'regString = "This is a sample AA2-11-11 string"  '<~~ Doesn't Match
    'regString = "This is a sample AA2-11-11 string"  '<~~ Doesn't Match

    regString = " " & regString & " "

    Set myRegExp = New RegExp

    With myRegExp
        .Global = True
        .Pattern = "\b[a-zA-Z]{2}\d{1,}-\d{2,}\b(?=[^-])"

        Set myMatches = myRegExp.Execute(regString)

        For Each myMatch In myMatches
            Debug.Print myMatch.Value
        Next
    End With
End Sub

Upvotes: 3

Related Questions