CODEWITHSUNDEEP
regexexcelvbaexcel-2010
rew
rew

Reputation: 375

Excel VBA RegEx Match word plus anything to word border

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

Upvotes: 0

Views: 938

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You probably want to use this pattern:

Tom\S*\b

This would match Tom followed by any number of non whitespace characters (zero or more), ending in a word boundary.

re.Pattern = "Tom\S*\b"
x = re.Replace(sample, "")
Debug.Print(x)

Upvotes: 1

adhy wijaya
adhy wijaya

Reputation: 509

Did you try this:

x = Replace(sample, re.Pattern, "")

Good luck.

Upvotes: 0

Related Questions