Batch Find and Replace Multiple Items at Once in Excel (VBA)

Here is a custom macro for finding and replacing several items in an Excel list.

This macro loops (runs) through a list of items on a table with two columns - old text and new text - and runs a find and replace operation for each row in the table using the text in the old text column as the Find text, and the one in the new text column as the Replace text. It then returns a "Completed" in the empty column right after the new text column to confirm success of each operation.

The macro is below. Lines you will need to edit to match your specific needs are highlighted in bold, with the guiding comment right in front of it (after the ' character):

Sub FindandReplaceMulti()
'
Dim oldTxt As String
Dim newTxt As String

For i = 1 To 33 'my list had 33 items to find and replace. Change 33 to the number of rows in your find-replace table
    
    oldTxt = Cells(i, 20).Value 'my old text was in Column T (the 20th column). Change the number 20 to match the position of your old text column
    newTxt = Cells(i, 21).Value 'my new text was in Column U (the 21st column). Change the number 21 to match the position of your new text column


    
    Sheets("data").Select 'change "data" to the name of the worksheet where the find-replace operation is to run
    Columns("I:I").Select 'change "I:I" to the specific range of cells you want to run the find-replace operation on


    
    Selection.Replace What:=oldTxt, Replacement:=newTxt, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
    Cells(i, 22).Value = "Completed" 'change 22 to the number of the column right after your new text column
    

Next i

End Sub