VBA Find & Replace Part or Full word in a text string

[VBA learner ITG]

Hi all,

I need your advice as I cannot seem to amend the code to ignore words that are part of a text string.

For Example:
So that it finds the WHOLE WORD "IL" ONLY??? I want it to replace IL, but it's replacing letters from names like, Bill, for example.

I have tried amending this partof the code to the below and it doesnt work.

 LookAt:=xlPart to LookAt:=xlWhole.

My Original code below:

Sub btn_find_replace_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws_client As Worksheet
Dim tbl As ListObject
Dim lrow As Range

Set wb = ActiveWorkbook
Set ws = wb.Sheets("MAIN BRIEF")
Set ws_client = wb.Sheets("CLIENT_FACING")

Set tbl = ws_client.ListObjects("Table1")
    For Each lrow In tbl.ListColumns(1).DataBodyRange.Rows
    find_str = lrow.Offset(0, 0)
    rep_str = lrow.Offset(0, 1)
    ws.Cells.Replace what:=find_str, Replacement:=rep_str, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next lrow
Set tbl = Nothing
Set ws = Nothing
Set ws_client = Nothing
Set wb = Nothing
End Sub

 

[JLGWhiz]

If you want it to only replace whole text then change this

LookAt:=xlPart

to this

LookAt:=xlWhole

However, if you want to replace the text group which may be part of another string or stand alone without changing any other string that may have part of the search text in it, that would take some more elaborate coding. You might get a better response if you can provide some examples of what you are searching for and what you are searching in.

 

[VBA learner ITG]

If you want it to only replace whole text then change this

to this

However, if you want to replace the text group which may be part of another string or stand alone without changing any other string that may have part of the search text in it, that would take some more elaborate coding. You might get a better response if you can provide some examples of what you are searching for and what you are searching in.

Picture 1 is the table which I am doing a find and replace on.
Picture 2 is the column which I am trying to replace.

For example if i was to try and find and replace "Gu" and replace it with "Gü" it is replacing the below words which is part of the sentence: "Güylian Seashell Bar Milk Truffle" even when I change the code to "xlWhole"

 

[JLGWhiz]

It is a common problem. Using the table with whole words you can use the xlWhole and it will ignore other words that contain your search search word in part. But trying to use part of a word like the Gu to Gü would change words like "vague", "guest", "argue", etc. when xlPart is used. Unfortunately, Excel was not designed with word processor functions in mind. Some people have success with RegEx, but I could never master that one.

 

[VBA learner ITG]

It is a common problem. Using the table with whole words you can use the xlWhole and it will ignore other words that contain your search search word in part. But trying to use part of a word like the Gu to Gü would change words like "vague", "guest", "argue", etc. when xlPart is used. Unfortunately, Excel was not designed with word processor functions in mind. Some people have success with RegEx, but I could never master that one.

Thank you for your time and reply. I have requested the code to be rewritten by a paid developer.

 

[JLGWhiz]

Thanks for the feedback,
regards, JLG

 

[VBA learner ITG]

Hopefully, the code below which now works will help anyone who encounters the issue I had.

Sub btn_find_replace_Click_v2()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws_client As Worksheet
Dim tbl As ListObject
Dim lrow As Range


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

Set wb = ActiveWorkbook
Set ws = wb.Sheets("sheet1")
Set ws_client = wb.Sheets("sheet2")

Set tbl = ws_client.ListObjects("Table1")
For Each lrow In tbl.ListColumns(1).DataBodyRange.Rows
    find_str2 = Null
    rep_str2 = Null
        find_str = lrow.Offset(0, 0)
        If Len(find_str) = 0 Then GoTo quit_prog:
        rep_str = lrow.Offset(0, 1)
        Debug.Print find_str
    For i = 1 To 3
        If i = 2 Then
                
            Debug.Print 2
        ElseIf i = 3 Then
            find_str2 = find_str & " "
            rep_str2 = "|" & rep_str & "|" & " "
        Else
            find_str2 = find_str
            rep_str2 = "|" & rep_str & "|"
        End If
        ws.Activate
        If i = 1 Then
            Columns("A").Select
            Selection.Replace What:=find_str2, Replacement:=rep_str2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, _
            SearchFormat:=False, ReplaceFormat:=False
            
            
        ElseIf i = 3 Then
            Columns("A").Select
            Selection.Replace What:=find_str2, Replacement:=rep_str2, _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
            SearchFormat:=False, ReplaceFormat:=False
            
            
        End If
    Next
Next lrow
quit_prog:
            Columns("A").Select
            Selection.Replace What:="|", Replacement:="", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
            SearchFormat:=False, ReplaceFormat:=False
            
Set tbl = Nothing
Set ws = Nothing
Set ws_client = Nothing
Set wb = Nothing

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

 

[thescoop]

Hi all,

I need your advice as I cannot seem to amend the code to ignore words that are part of a text string.

For Example:
So that it finds the WHOLE WORD "IL" ONLY??? I want it to replace IL, but it's replacing letters from names like, Bill, for example.

I have tried amending this partof the code to the below and it doesnt work.

 LookAt:=xlPart to LookAt:=xlWhole.

My Original code below:

Sub btn_find_replace_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws_client As Worksheet
Dim tbl As ListObject
Dim lrow As Range

Set wb = ActiveWorkbook
Set ws = wb.Sheets("MAIN BRIEF")
Set ws_client = wb.Sheets("CLIENT_FACING")

Set tbl = ws_client.ListObjects("Table1")
    For Each lrow In tbl.ListColumns(1).DataBodyRange.Rows
    find_str = lrow.Offset(0, 0)
    rep_str = lrow.Offset(0, 1)
    ws.Cells.Replace what:=find_str, Replacement:=rep_str, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next lrow
Set tbl = Nothing
Set ws = Nothing
Set ws_client = Nothing
Set wb = Nothing
End Sub

The simple solution is to search for whole words WITH spaces either side.
Do the following:
1> Pad your sentence with a leading and ending space to ensure that the first and last words get processed.
2> grab your findcell.value and pad it with a space either side.
3> grab your replacementcell.value and pad with a space either side.
4> Perform the replacement using your original 'replace' method - this will then only replace whole words and not characters within words.
5> Finally, take the space padding off either side of the finished (changed) sentence.
Sorry that I have seen your post after you've paid an expert, but I hope this simple solution helps your thought processes in the future.