Find and Replace Multiple Strings in Excel

The below VBA code, posted here by the Spreadsheet Guru, can be used to find and replace multiple values in an Excel workbook. List the terms you want to find in column A of 'Sheet1' and the terms they should replace in column B. Select the ranges (not the whole columns) and then press CTRL + T to add the data as 'Table1', the default name.

Put the VBA code in a new module.

Go to View . . . Macros and run the code.

The macro will replace anywhere the listed terms appear on other worksheets. So this worksheet:

. . . is changed to:

This macro will work for strings which don't consist of the entire contents of a cell.

Sub Multi_FindReplace() 'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table 'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

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("Sheet1").ListObjects("Table1")

'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) For Each sht In ActiveWorkbook.Worksheets If sht.Name <> tbl.Parent.Name Then sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False End If Next sht Next x

End Sub