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

VBA learner ITG

Board Regular
Joined
Apr 18, 2017
Messages
205
Office Version
  1. 365
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.

VBA Code:
 LookAt:=xlPart to LookAt:=xlWhole.

My Original code below:

VBA Code:
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Apr 18, 2017
Messages
205
Office Version
  1. 365
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"
 

Attachments

  • FIND AND REPLACE DATA.JPG
    FIND AND REPLACE DATA.JPG
    90.1 KB · Views: 6
  • COLUMN OF DATA WITHIN SENTANCE IT IS TRYING TO FIND AND REPLACE.JPG
    COLUMN OF DATA WITHIN SENTANCE IT IS TRYING TO FIND AND REPLACE.JPG
    147.5 KB · Views: 6

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Apr 18, 2017
Messages
205
Office Version
  1. 365

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Thanks for the feedback,
regards, JLG
 

VBA learner ITG

Board Regular
Joined
Apr 18, 2017
Messages
205
Office Version
  1. 365
Hopefully, the code below which now works will help anyone who encounters the issue I had.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,313
Messages
5,623,929
Members
416,000
Latest member
teabag

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top