Skip to content

Find & Replace Multiple Words or Characters at Once in Excel | 3 Methods VLOOKUP, SUBSTITUTE, VBA Macro

    In this video I demonstrate how to find and replace multiple words at once in Excel. I explore three methods that can be used to achieve this.

    Click here to download the featured file

    Video Table of Contents

    00:00 Introduction

    00:44 METHOD 1: Use VLOOKUP or XLOOKUP for whole word find and replace

    Here’s the formulas used with this method.

    =IFNA(VLOOKUP(B3,$F$3:$G$5,2,0),B3)

    =XLOOKUP(B3,$F$3:$F$5,$G$3:$G$5,B3)

    03:23 METHOD 2: Use the SUBSTITUTE function for partial match find and replace

    Here’s the formula used with this method.

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,$E$3,$F$3),$E$4,$F$4),$E$5,$F$5)

    05:36 METHOD 3: Use a VBA Macro for whole word or partial match find and replace (lots of replacements)

    Here’s the code used in with this method.

    Sub MultiReplace()

Dim ListItem As Range
Dim ListToReplaceWithin As Range
Dim ListOfThingsThatWillChange As Range

On Error GoTo ErrorHandler

Set ListToReplaceWithin = Application.InputBox(Prompt:="Select the list you want to replace within:", Title:="Replace Items", Type:=8)
Set ListOfThingsThatWillChange = Application.InputBox(Prompt:="Select the list of items that you want to change:", Title:="Replace With", Type:=8)

For Each ListItem In ListOfThingsThatWillChange
    ListToReplaceWithin.Replace What:=ListItem, Replacement:=ListItem.Offset(0, 1)
Next ListItem

Exit Sub

ErrorHandler:
Exit Sub

End Sub