vba excel and regex

Question

i have some code (shown below) that i got of a site, it allows me to pass in a string, findstring and replace string and it will replace only a full word if it is found. the issue i am having is i have words that have a minus sign behind it and it is treating it like a seperate word.

for example

Howdy Howdy-1

When i tell it to replace Howdy with doodie, it replaces both Howdy and Howdy-1 with doodie and doodie-1 respectively. I want it to replace the one word but not the one that has -1 or any other symbol behind the word like Howdy#1

Is this possible. In my code howdy and doodie are coming from cells in a spreadsheet

function that is doing the 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 '
    'requires reference to Microsoft VBScript Regular Expressions '
    'Dim re As RegExp '
    'Set re = New RegExp '
    '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

Please edit your question title to contain something more than just a repetition of the tag information. Your title should describe the problem you're having or question you're asking in a way that will be useful to future users here who see it in a list of search results. Thanks. — Ken White
– Ken White, Commented Sep 19, 2018 at 1:21

Kenneth K. · Accepted Answer · 2018-09-19 14:56:00Z

1

Your problem is that \b matches a "word boundary," which is any place where either a letter, number, or underscore adjoins a character that is not one of the aforementioned--or where it adjoins an anchor like $ (end of string). The Howdy-1 has a word boundary between the y and the -, which is why your pattern matches there. If you want to match on the whole word, then you'd need to modify your pattern to include a space as well as anchors:

re.Pattern = "(^| )" & strFind & "( |$)"

The alternation (|) will allow you to look for your word where it either is at the beginning of the string (^) with a trailing space, or is at the end of the string ($) with a leading space, or is in the middle of the string with a space on either side of it.

Since the spaces are captured via the capture groups (i.e. parentheses), you can include them in your replacement operation to ensure that they are preserved:

RegExpReplaceWord = re.Replace(strSource, "$1" & strReplace & "$2")

$1 refers to capture group 1, which is the first set of parentheses (reading right to left), and $2 refers to capture group 2, which is the second set of parentheses. If a space is captured by either group, then it will be appended in the replacement, respectively. If start-of-string or end-of-string is "captured" by either group, then effectively empty string is captured, and that is what will be appended in the replacement.

edited Sep 19, 2018 at 14:56

answered Sep 19, 2018 at 2:17

Kenneth K.

3,0452 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mitaithecat Over a year ago

This kind of works but it takes out the spaces between the words. Although it fixes the Howdy-1 and realizes that it isnt Howdy, on other ones that are replacing words like "The ball is green" search for ball replace with Truck it is returning "TheTruckis green"

Kenneth K. Over a year ago

In your replacement, you can refer to capture groups 1 and 2, which are enabled by the use of parentheses above. I'll update the answer.

mitaithecat Over a year ago

Actually i found a way where this works, just added the spaces back in on my replace and trimmed it if it was at the beginning or end of the string. Great answer

E. Sun · Accepted Answer · 2018-09-19 02:11:06Z

0

put $ at the end of your regex pattern so it knows it must be the end of the string. For more syntax like this try looking at:

https://www.mikesdotnetting.com/article/44/vbscript-regular-expressions-cheat-sheet

answered Sep 19, 2018 at 2:11

E. Sun

1,39413 silver badges17 bronze badges

Collectives™ on Stack Overflow

vba excel and regex

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related