How to Find and Replace Multiple Words from a List in Excel

This is an overview:

Method 1 – Use the Nested SUBSTITUTE Function to Replace Multiple Words in Word

Steps:

Select C5 and use the following formula.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:B11,E5,F5),E6,F6),E7,F7)

Press Enter.

This is the output.

Formula Breakdown

B5:B11 is set as the text range.
In the first SUBSTITUTE function, the value in E5 is substituted with the value in F5.
E6 is substituted with F6 and E7 with F7.

Method 2 – Find and Replace Multiple Words Using the XLOOKUP Function

Steps:

Select C5 and use the following formula.

=XLOOKUP(B5, $E$5:$E$7, $F$5:$F$7, B5)

Press Enter.
Drag down the Fill Handle to see the result in the rest of the cells.

In the XLOOKUP function, B5 is the lookup_value, E5:E7 the lookup_array, F5:F7 the return_array, and B5 refers to if_not_found.

This is the output.

Method 3 – Using the IFNA and the VLOOKUP Functions to Find and Replace Multiple Words

Steps:

Select C5 and use the following formula.

=IFNA(VLOOKUP(B5, $E$5:$F$7, 2, FALSE), B5)

Press Enter.
Drag down the Fill Handle to see the result in the rest of the cells.

Formula Breakdown

The VLOOKUP function looks for the value of B5 in E5:F7, 2 is the col_index_num and FALSE is the range_lookup to find an Exact match.
The resultant is set as the value in the IFNA function and B5 as value_if_na.

This is the output.

Method 4 – Create a Custom LAMBDA Function to Find and Replace from an Excel List

Steps:

Press Ctrl +F3.
In Name Manager, click New.

In the New Name box, enter a name. Here, MReplace.
Use the following formula in Refers to.

=LAMBDA(text,old,new, IF(old<>"", MReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))

Click OK.

Click Close.

Select C5 and use the following formula.

=MReplace(B5:B11,E5,F5)

Press Enter.

The words will be replaced.

Method 5 – Find and Replace Multiple Words Using a User-Defined Function

Steps:

Go to the Developer tab >> click Visual Basic.

In Microsoft Visual Basic for Applications, click Insert >> select Module.

Enter the following code in the Module.

Function MultiWordReplace(InputRng As Range, FindRng As Range, ReplaceRng As Range) As Variant()
  Dim array_1() As Variant
  Dim arFindReplace(), strtmp As String
  Dim index_find_cur_row, count_found_rows As Long
  Dim Index_cur_row_source, Index_cur_Colm_source, count_rows_source, count_input_colm As Long
  count_rows_source = InputRng.Rows.Count
  count_input_colm = InputRng.Columns.Count
  count_found_rows = FindRng.Rows.Count
  ReDim array_1(1 To count_rows_source, 1 To count_input_colm)
  ReDim arFindReplace(1 To count_found_rows, 1 To 2)
  For index_find_cur_row = 1 To count_found_rows
    arFindReplace(index_find_cur_row, 1) = FindRng.Cells(index_find_cur_row, 1).Value
    arFindReplace(index_find_cur_row, 2) = ReplaceRng.Cells(index_find_cur_row, 1).Value
  Next
   For Index_cur_row_source = 1 To count_rows_source
  For Index_cur_Colm_source = 1 To count_input_colm
    strtmp = InputRng.Cells(Index_cur_row_source, Index_cur_Colm_source).Value
    For index_find_cur_row = 1 To count_found_rows
      strtmp = Replace(strtmp, arFindReplace(index_find_cur_row, 1), arFindReplace(index_find_cur_row, 2))
    Next
    array_1(Index_cur_row_source, Index_cur_Colm_source) = strtmp
    Next
  Next
  MultiWordReplace = array_1
End Function

Code Breakdown

The MultiwordReplace user-defined function is created.
Variables are declared.
Source rows, input columns and found_rows are counted.
Variables are redeclared .
A For loop replaces the values.

Click Save.

Select C5 and enter the following formula.

=MultiWordReplace(B5:B11,E5:E7,F5:F7)

Press Enter.

This is the output.

Method 6 – Using VBA to Replace Multiple Words from an Excel List

Steps:

Insert a module (follow the steps described in Method 5).
Use the following code.

Sub Country_Replace()
Dim range_1 As Range, Sourcerange_1 As Range, Replacerange_1 As Range
On Error Resume Next
Set Sourcerange_1 = Application.InputBox("Source list:", "Bulk Replace", Application.Selection.Address, Type:=8)
Err.Clear
If Not Sourcerange_1 Is Nothing Then
Set Replacerange_1 = Application.InputBox("Replace by range:", "Bulk Replace", Type:=8)
Err.Clear
If Not Replacerange_1 Is Nothing Then
Application.ScreenUpdating = False
For Each range_1 In Replacerange_1.Columns(1).Cells
Sourcerange_1.Replace what:=range_1.Value, replacement:=range_1.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End If
End If
End Sub

Code Breakdown

The Country_Replace Sub Procedure is created.
Range_1 is declared as Range, Sourcerange_1 and Replacerange_1 as Range.
An InputBox is set as Sourcerange_1.
The code checks If Sourcerange_1 has no values and adds another InputBox as Sourcerange_1.
It checks If Replacerange_1 has no values and sets ScreenUpdating as False.
A loop is used for range_1 in Replacerange_1 to replace the values.

Save the code and go back to the sheet.
In the Developer tab >> click Macros.

Select Country_Replace.
Click Run.

A Bulk Replace box is displayed: enter B5:B11 in Source List.
Click OK.

Enter D5:E7 in Replace by range.
Click OK.

This is the output.

Method 7 – Find and Replace Multiple Words Using the REPLACE Function

Steps:

Select C5 and use the following formula.

=REPLACE(B5:B9,1,2,F5:F9)

Press Enter.

This is the output.

In the REPLACE function, B5:B9 is declared as old_text, 1 as start_num, 2 as num_chars and F5:F9 as new_text.

Method 8 – Combine the IF, REPLACE and LEN Functions to Find and Replace Multiple Words from an Excel List

Steps:

Enter the following formula in C5.

=IF(LEN(B5)=2,REPLACE(B5,1,2,IF(B5="US","United States",IF(B5="SP","Spain",IF(B5="UK","United Kingdom")))),B5)

Press Enter.
Drag down the Fill Handle to see the result in the rest of the cells.

Formula Breakdown

A Nested-IF function is used to change the value of B5 if it is equal to “US”, “SP” or “UK”.
In the REPLACE function, B5 is declared as old_text, 1 as start_num, 2 as num_chars and the result of the Nested-IF function as new_text.
Another IF function checks if the length of the text in B5 is equal to 2 using the LEN function. If this function is True, it will return the resultant value of the REPLACE function. Otherwise, it will return the value in B5.