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