Find and replace macro targeting words within words - can it target only whole words within a string?

[dougmarkham]

Hi Folks,

I am attempting to use VBA to modify address data because the first two address columns have character limits and my data tends to exceed the limits.
I wish to modify text strings within cells i.e., to find and replace words within text strings with abbreviations. For the below code, I have compiled a fndList and rplcList (table 2) to achieve this.

I am using this code:

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant


'Create variable to point to your table
  Set tbl = Worksheets("Abbrev").ListObjects("Table2")


'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
  
'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2


'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
          Selection.Replace what:=myArray(fndList, x), replacement:=myArray(rplcList, x), _
            [COLOR=#008000][B]LookAt:=xlPart[/B][/COLOR], SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
  Next x


End Sub

As an example of the problem.

I'm trying to replace words such as:
and--with-->&
field--with-->fld

The below code works but is altering place names like:
Sunderland--to-->Sunderl&
Sheffield---to-->Sheffld

I have to stipulate a partial match because my cell contents are e.g., Sunderland and Shields Building Society

LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False

In this example, I would like: and--to be-->&; Building--to be-->Bldg and Society--to be-->Soc (Sunderland & Shields Bldg Soc); therefore, I need a partial match.

At the moment, as it's finding partial matches 'inside words' in a string, I wonder if there is a way to modify the code to allow partial match but only against whole words within a string i.e., so Sunderland would remain Sunderland but "and" would become "&"?

If it is possible to achieve this, would anybody be willing to help me modify this code please?

Kind regards,

Doug

 

[Tim_Excel_]

Doug,

Have you thought of including spaces into your fndList? This way, when searching for " and " instead of "and", only the single word "and" would be replaced. Of course you'd have to include spaces in the rplcList as well.

 

[dougmarkham]

Doug,

Have you thought of including spaces into your fndList? This way, when searching for " and " instead of "and", only the single word "and" would be replaced. Of course you'd have to include spaces in the rplcList as well.

Hi Tim,

That is exactly what I hadn't thought of---works a treat, and so simple!
Thanks very much for you help!

Kind regards,

Doug.

 

[Tim_Excel_]

Glad to have helped!