Search for a phrase with a wildcard to represent a whole word

[prupert]

Hi

I have the following macro that I use to do a find/replace text in a selction of cells, that I can use to harmonise phrases into consistent terms. It reads through a list of phrases on a sheet called Phrases and replaces them with the phrase in the adjacent cell.

I would like to use a wildcard in this find / replace macro so that, for example:

not made a payment
not made this payment
not made that payment

all match the search term:

"not made * payment"

However, this doesnt' seem to work.

Does anyone know what wildcard pattern I can use so that the search phrase uses a wildcard for a whole word, so I can search for multiple phrases with just one search term?

Here is the find / replace macro:

    Dim RgExp As Object
    Dim cel As Range, rg As Range
    Dim Y As Variant, tmp As Variant
    Dim i As Long, nFindReplace As Long
    Dim FindReplacePrompt As String
        Dim InputSheet As Worksheet
    Dim WordListSheet As Worksheet
    Dim r As Long
    Dim txt As String
    Dim wordCnt As Long
    Dim AllWords As Range
    Dim PC As PivotCache
    Dim PT As PivotTable
 
 
    FindReplacePrompt = "I couldn't find the Find/Replace strings at Sheet2!A1:Bxx. Please select them now." & _
    "  No blanks allowed in first column!"
    Application.StatusBar = "THE MAGIC IS HAPPENING....."
    If Selection.Cells.Count = 1 And Selection.Cells(1, 1) = "" Then
        MsgBox "Please select some cells to run the macro on, then try again."
        Exit Sub
    End If
 
    Application.ScreenUpdating = False
 
    PuncChars = Array(".", ",", ";", ":", "'", "!", "#", _
        "$", "%", "&", "(", ")", "_", "--", "+", _
        "=", "~", "/", "\", "{", "}", "[", "]", """", "?", "*")
    Application.StatusBar = "THE MAGIC IS HAPPENING - REMOVING PUNCTUATION AND CAPATILISING....."
'   Loop until blank cell is encountered
    For Each x In Selection
      x.Value = UCase(x.Value)
      For i = 0 To UBound(PuncChars)
            txt = UCase(" " & RemovePunctuation(x.Text) & " ")
            txt = Replace(txt, PuncChars(i), "")
      Next i
      txt = WorksheetFunction.Trim(txt)
    Next
 
 
     'Populate the array variable Y with Find/Replace strings. Default source is Sheet2, A1:Bxx
    Application.StatusBar = "THE MAGIC IS HAPPENING - RUNNING FIND / REPLACE....."
    On Error Resume Next
    Set rg = ThisWorkbook.Worksheets("Phrases").Range("A1")
    If rg Is Nothing Then
        Set rg = Application.InputBox(prompt:=FindReplacePrompt, Title:="Source of Find/Replace strings", Type:=8)
        If rg Is Nothing Then Exit Sub
    Else
        If rg.Cells(1, 1) = "" Then
            Set rg = Application.InputBox(prompt:=FindReplacePrompt, Title:="Source of Find/Replace strings", Type:=8)
            If rg Is Nothing Then Exit Sub
        Else
            Set rg = Range(rg, rg.End(xlDown).Offset(0, 1))
 
        End If
    End If
    On Error GoTo 0
    Y = rg.Value
    nFindReplace = UBound(Y)
 
    Set RgExp = CreateObject("VBScript.RegExp")
    With RgExp
        .Global = True
         '.IgnoreCase = True  'True if search is case insensitive. False otherwise
    End With
 
    Application.ScreenUpdating = False
    For Each cel In Selection
        For i = 1 To nFindReplace
 
            RgExp.Pattern = "\b" & Y(i, 1) & "\b"
            tmp = RgExp.Replace(cel.Value, Y(i, 2))
            If cel.HasFormula Then
                If cel.Value <> tmp Then cel.Formula = tmp
            Else
                cel.Value = tmp
            End If
            Application.StatusBar = "THE MAGIC IS HAPPENING - CHECKING WORD: " & i
        Next i
    Next cel
 
    Set RgExp = Nothing
    Application.ScreenUpdating = True

 

[John_w]

Have you tried using the Like operator to compare strings instead of regular expressions? It may achieve what you want, without the complexity of regex's. Something like this:

If "not made a payment" Like "not made * payment" Then MsgBox "Matches"

If you really need the regex matching, then convert the wildcarded phrase to a regex pattern by replacing the * in the phrase with the regex equivalent:

RegEx.pattern = Replace(wildcardPhrase, "*", "\w*")

Or you might use "\w+" if the * wildcard must be a word.

 

[AlphaFrog]

Match anything in between.
"not made .* payment"

Match exactly one word (any word) in between.
"not made +[^ ]+ +payment"

Match specifically one of three words in between
"not made (a|this|that) payment"

 

[prupert]

Match anything in between.
"not made .* payment"

Match exactly one word (any word) in between.
"not made +[^ ]+ +payment"

Match specifically one of three words in between
"not made (a|this|that) payment"

Excellent, thanks all for your responses.

I shall try the above to see how it works, since it looks like it should be perfect....