Having a hard time finding the right regex pattern in Excel VBA.
Given the following example:
"Tom wore a short red shirt on Tom's birthday. As you might have guessed the Toms have the same birthday."
I want to match all Tom plus any character in any amount up to the word border. So in the example, Tom, Tom's, and Toms would match. I then need to remove them from the string including space after the word, so when I'm done the string looks like this.
wore a short red shirt on birthday. As you might have guessed the have the same birthday.
Can this be done with one pattern?
Here is my sample code. I have tried several combinations with no success.
Sub test() Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.IgnoreCase = True sample = "Tom wore a short red shirt on Tom's birthday. As you might have guessed the Toms have the same birthday." Debug.Print (sample) 're.Pattern = "Tom[a-zA-Z'-]+\b" re.Pattern = "Tom+\b" x = re.Replace(sample, "") Debug.Print (x) End Sub