Search and replace WORDS ONLY

Question

I am looking for a way to find and replace ONLY ALL WORDS. Since all the words in my case can be separated not only by space, but.,;: /? etc. I cannot imagine an efficient way to encode this.

Basically, I'm looking for something like this

replace([address], ***--list of separators, like .,;:/?--*** & [replacewhat] & ***--list of separators, like .,;:/?--*** ," " & [replacewith] & " ") 

I don’t know how to pass the list of delimiters right away instead of launching the replace function once for each COMBINATION of delimiters (which, combined with the 300 words that I replace, will equal an insane number of queries)

Answer 1

You can make a replacement with a regular expression using the pattern with a marker \b (for the word boundary) before and after the word you want to replace.

 Public Function RegExpReplaceWord(ByVal strSource As String, _ ByVal strFind As String, _ ByVal strReplace As String) As String ' Purpose : replace [strFind] with [strReplace] in [strSource] ' Comment : [strFind] can be plain text or a regexp pattern; ' all occurences of [strFind] are replaced ' early binding requires reference to Microsoft VBScript ' Regular Expressions: 'Dim re As RegExp 'Set re = New RegExp ' with late binding, no reference needed: Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True 're.IgnoreCase = True ' <-- case insensitve re.pattern = "\b" & strFind & "\b" RegExpReplaceWord = re.Replace(strSource, strReplace) Set re = Nothing End Function 

As written, the search is case sensitive. If you want case insensitive, include this line:

 re.IgnoreCase = True 

In the Immediate window ...

 ? RegExpReplaceWord("one too three", "too", "two") one two three ? RegExpReplaceWord("one tool three", "too", "two") one tool three ? RegExpReplaceWord("one too() three", "too", "two") one two() three ? RegExpReplaceWord("one too three", "to", "two") one too three ? RegExpReplaceWord("one too three", "t..", "two") one two three 

... and for your range of delimiters ...

 ? RegExpReplaceWord("one.too.three", "too", "two") one.two.three ? RegExpReplaceWord("one,too,three", "too", "two") one,two,three ? RegExpReplaceWord("one;too;three", "too", "two") one;two;three ? RegExpReplaceWord("one:too:three", "too", "two") one:two:three ? RegExpReplaceWord("one/too/three", "too", "two") one/two/three ? RegExpReplaceWord("one?too?three", "too", "two") one?two?three ? RegExpReplaceWord("one--too--three", "too", "two") one--two--three ? RegExpReplaceWord("one***too***three", "too", "two") one***two***three