Regular Expression Help

Hello,

I'm trying to use Regular Expression in a custom function to find two words and add a comma between them.

I have the following code but the comma does not get added. I simply get "Avenue Apt." when I want to see "Avenue, Apt." etc.

Thank you for your help!


Function FIXAPARTMENT(strLookIn As String) As String
Dim objRegEx

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Global = True
objRegEx.IgnoreCase = False

FIXAPARTMENT = strLookIn

objRegEx.Pattern = "\bAvenue Apt.\b"
FIXAPARTMENT = objRegEx.Replace(FIXAPARTMENT, "Avenue, Apt.")

objRegEx.Pattern = "\bBoulevard Apt.\b"
FIXAPARTMENT = objRegEx.Replace(FIXAPARTMENT, "Boulevard, Apt.")

objRegEx.Pattern = "\bStreet Apt.\b"
FIXAPARTMENT = objRegEx.Replace(FIXAPARTMENT, "Street, Apt.")

END FUNCTION

Thanks for your help. But I'm still not sure how to enter it.

Hi !

Try this :

PHP Code: 

Sub Demo()
       S$ = "VBA Avenue Apt. test"
       P& = InStr(S, "Avenue Apt.")
    If P Then
        Mid(S, P + 6) = ","
        MsgBox S
    End If
End Sub 


Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

When I try escaping the comma with a backslash, I get the comma but also the backslash in the output!

objRegEx.Pattern = "\bBoulevard Apt.\b"
FIXAPARTMENT = objRegEx.Replace(FIXAPARTMENT, "Boulevard\, Apt.")

Results: 50 Maple Boulevard\, Apt. 3

I just want the comma to appear as literal text. Everything else is working!

A period in a pattern is a Meta Character, so you need to escape with a back slush.

i.e

objRegEx.Pattern = "\bAvenue Apt\.\b"

Like I said, everything is working. It's finding the pattern properly, it's just not fixing it by adding a comma in the replacement output.

Sub test()
    MsgBox FIXAPARTMENT("50 Maple Boulevard Apt. 3")
End Sub

Function FIXAPARTMENT(strLookIn As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\b(Avenue|Boulevard|Street)(?= *Apt\.)"
        If .test(strLookIn) Then
            FIXAPARTMENT = .Replace(strLookIn, .Execute(strLookIn)(0).submatches(0) & ", ")
        End If
    End With
End Function

Thanks Jindon. What would I add to accommodate other variations for Apt. like No., Unit, Ste., Bldg. etc. or sometimes it's just the number like:

50 Maple Boulevard 3

I still need the comma 50 Maple Boulevard, 3

Thank you.

Also, curious why I couldn't just add

this & ", ")

to the end of my code to get the comma like this.

FIXAPARTMENT = objRegEx.Replace(FIXAPARTMENT, "Street & ",")

I get a #VALUE error instead.

If you upload a workbook with before/after, I can give you a suggestion.

BTW, the way you are using Regular Expression is not effective.
It would be much easier like

Sub test()
    Dim e, FIXAPARTMENT As String
    FIXAPARTMENT = "Avenue Apt."
    For Each e In Array("Avenue", "Boulevard", "Street")
        If InStr(FIXAPARTMENT, e & " Apt.") Then
            FIXAPARTMENT = Application.Replace(FIXAPARTMENT, InStr(FIXAPARTMENT, " Apt."), , ",")
            Exit For
        End If
    Next
    MsgBox FIXAPARTMENT
End Sub