Replace word in a sentence

Hello,

I am a new member in the forum and I will need your help.

I use the following code. I want to make Replace only in the case which there is a whole word in a part of a cell, but not in part of specific characters.

Example: In the code i want to replace the word “to” but replaces all the words that contains “to”. For example in the word together lets only the characters gether, and in the word tonight lets only the characters night etc.

Sub FindReplace()
    Dim rngFalse As Range
    Dim oNameFalse As String
    Set rngFalse = Range("L3:L4535")

        oNameFalse = "to"

        Columns("M:M").Select
        Selection.Replace What:=oNameFalse, Replacement:="", LookAt:=xlPart, _
                          SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False
    
End Sub

Thank you in advance,

Regards,
Dimitris

Change :- oNameFalse = "to" to oNameFalse = " to " and perhaps replace to :- " "

Did you try putting a space before and after the word "to"? Like this:

Sub FindReplace()
    Dim rngFalse As Range
    Dim oNameFalse As String
    Set rngFalse = Range("L3:L4535")

        oNameFalse = " to "

        Columns("M:M").Select
        Selection.Replace What:=oNameFalse, Replacement:=" ", LookAt:=xlPart, _
                          SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False
    
End Sub

Hi,

Thank you all for your help.

Dimitris