Find text in word in the cell and replace whole word

How to make that if the word in cell have ab change whole word for ab
find ab replace whole word.JPG

The function find and replace find the word but replace only that part of word.

Sub Test()
    '
    ' Test Macro
    '
    Dim i As Integer
    
    For i = 1 To 2
    
        Columns("A:A").Replace What:=Range("B" & i), _
                               Replacement:=Range("C" & i), _
                               LookAt:=xlPart, _
                               SearchOrder:=xlByRows, _
                               MatchCase:=False, SearchFormat:=False, _
                               ReplaceFormat:=False
    Next i
    
End Sub

Try chnge to

        Columns("A:A").Replace What:="*" & Range("B" & i) & "*", _
                               Replacement:=Range("C" & i), _
                               LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, _
                               MatchCase:=False, SearchFormat:=False, _
                               ReplaceFormat:=False

jindon thank you very much. Works great. You did far more than on the picture but actually this was exacly what i was looking for.
I think it was not for begginer, but i will analise it to get how it works.

Can i ask you just one more thing.
Can you make this code to put to the last column only the replaced texts?
only replaced.JPG

OOps, I didn't look at the picture... bit more complex than I thought...

Try

Sub test()
    Dim r As Range, myPtn As String, i As Long, m As Object, temp, x
    With Range("b3", Range("b" & Rows.Count).End(xlUp))
        myPtn = Join(Filter(Evaluate("transpose(if(" & .Address & "<>""""," & _
        .Address & ",char(2)))"), Chr(2), 0), Chr(2))
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "([$()\-\^|\\{}\[\]*+?.])"
        myPtn = Replace(.Replace(myPtn, "\$1"), Chr(2), "|")
        .Pattern = "\S*(" & myPtn & ")\S*"
        For Each r In Range("a3", Range("a" & Rows.Count).End(xlUp))
            temp = r.Value
            If .test(r.Value) Then
                For i = .Execute(r.Value).Count - 1 To 0 Step -1
                    Set m = .Execute(r.Value)(i)
                    x = Application.VLookup(m.submatches(0), Columns("b:c"), 2, False)
                    temp = Application.Replace(temp, m.firstindex + 1, m.Length, x)
                Next
            End If
            r(, 4).Value = temp
        Next
    End With
End Sub

Grens

No one wants to work with picture, I normally do not post the code, so better upload a workbook.
Not tested.

Sub test()
    Dim r As Range, myPtn As String, m As Object, temp, x
    With Range("b3", Range("b" & Rows.Count).End(xlUp))
        myPtn = Join(Filter(Evaluate("transpose(if(" & .Address & "<>""""," & _
        .Address & ",char(2)))"), Chr(2), 0), Chr(2))
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "([$()\-\^|\\{}\[\]*+?.])"
        myPtn = Replace(.Replace(myPtn, "\$1"), Chr(2), "|")
        .Pattern = "\S*(" & myPtn & ")\S*"
        For Each r In Range("a3", Range("a" & Rows.Count).End(xlUp))
            temp = ""
            If .test(r.Value) Then
                For Each m In .Execute(r.Value)
                    x = Application.VLookup(m.submatches(0), Columns("b:c"), 2, False)
                    temp = temp & IIf(temp <>"",", ","") & x
                Next
            End If
            r(, 4).Value = temp
        Next
    End With
End Sub

Thank you it's working great just puts the comma between the text.
I tried to make this code without commas but it's still to difficult for me.
Could you just make this end result without commas?
replace.JPG

Sorry for not adding excel file, from now on i will add it

Originally Posted by Grens

Thank you it's working great just puts the comma between the text.
I tried to make this code without commas but it's still to difficult for me.
Could you just make this end result without commas?

Grens

Just change

temp = temp & IIf(temp <>"",", ","") & x

to

temp = temp & x