Replace only whole word

[capson]

Hello,

I want to replace only whole words in a cell

Also, I do not want to use Replacement, I want something I can use with Arrays

The function does a good job of finding only the whole words but is there a way to get it replace the whole word with the MatchExprValue

Ex
cell value =HUD, HOPHUD, Justice SystemHUD, Department of Health, Department of HUD Labor, DeparHUDtment of Social, HUD


Replace HUD with ZZZ


cell value =
ZZZ, HOPHUD, Justice SystemHUD, Department of Health, Department of ZZZ Labor, DeparHUDtment of Social, ZZZ


Thanks

Sub Test_SSS()
 Dim i As Long, m As Boolean
 
    For i = 2 To LastRow    
        m = WordMatch(Range("C" & i), Range("E1"))
        MsgBox "**" & Range("C" & i) & "**  matches  **" & Range("E1") & "**  -> " & m
    Next


End Sub


Function WordMatch(Source As String, MatchExprValue As String) As Boolean
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
    RE.IgnoreCase = False
    RE.Pattern = "\b" & MatchExprValue & "\b"
    WordMatch = RE.test(Source)
End Function

 

[Peter_SSs]

Not entirely clear to me. Can you clarify what you have, where, and exactly what you are trying to end up with, and where?