Nsryjdtyk

I am using the code below, to do a find and replace on excel:

Sub abbrev()

Dim abvtab() As Variant

Dim ltsheet As Worksheet

Dim datasheet As Worksheet

Dim lt As Range

Dim i As Long

Set ltsheet = Sheets("sheet2")

' REFERENCE TO SINGLE WORKSHEET

'  Set datasheet = Sheets("ACTIVE_DIRECTORY_User")

Set lt = ltsheet.Range("A2", ltsheet.Range("B2").End(xlDown))

abvtab = lt

For Each datasheet In Worksheets

If datasheet.Name <> ltsheet.Name Then

For i = 1 To UBound(abvtab)

datasheet.Cells.Replace What:=abvtab(i, 1), Replacement:=abvtab(i, 2), 

LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _

ReplaceFormat:=False

Next i

End If

Next datasheet

End Sub

However it doesn't seem to be able to find parts of the words for example if I want to replace the word book the code ignores the word bookshelves

Is there a way I can set the code above to match the exact word regardless if its part of a sentence or if it has a space for example?

in the end I had to abandon vba and instead I used C# Regex.Replace there was many issues with the find and replace via excel so the solution below seemed to work well, although the below worked i also had to abandon it as csv was the wrong file format for what i needed I'll be using mysql instead, i pasted the code below either way just in case someone might benefit from it and use it to do find and replace at scale

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.IO;

using System.Text.RegularExpressions;





namespace ConsoleApp10

{

class Program

{

static void Main(string args)

{

string SAR_CONTACTS = @"C: UsersDesktopSAR_CONTACTS.csv";

string SAR_CONTENT = @"C: UsersDesktopSAR_CONTENT.csv";

string READ_SAR_CONTACTS;

using (StreamReader streamReader = new StreamReader(SAR_CONTENT, Encoding.UTF8))

READ_SAR_CONTACTS = streamReader.ReadToEnd();



string SAR_CONTACTS_FILE = File.ReadAllText(SAR_CONTACTS);

string SAR_CONTENT_FILE = SAR_CONTACTS_FILE.Replace("rn", "|");

SAR_CONTENT_FILE = SAR_CONTENT_FILE.Remove(SAR_CONTENT_FILE.Length - 1);

string SAR_CONTENT_CENSORED = Regex.Replace(READ_SAR_CONTACTS, SAR_CONTENT_FILE, 

 "######", RegexOptions.IgnoreCase);

File.WriteAllText(@"C: UsersDesktopSAR_CONTENT_FILTERED.csv", 

 SAR_CONTENT_CENSORED);

}

}

}

How about this?

Sub Multi_FindReplace()



Dim sht As Worksheet

Dim fndList As Variant

Dim rplcList As Variant

Dim x As Long



fndList = Array("*" & "Ca" & "*", "United States", "Mexico")

rplcList = Array("CAN", "USA", "MEX")



'Loop through each item in Array lists

  For x = LBound(fndList) To UBound(fndList)

    'Loop through each worksheet in ActiveWorkbook

      For Each sht In ActiveWorkbook.Worksheets

        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _

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

          SearchFormat:=False, ReplaceFormat:=False

      Next sht



  Next x



End Sub

Notice the wildcard characters: "*" & "Ca" & "*"

Before:

After: