# How to Find and Replace Multiple Words from a List in Excel – 8 Methods

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:F72 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.

Read More: How to Find and Replace in Excel Column

### 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.

This is the output.

## How to Find & Replace a Single Word Multiple Times from an Excel List

Steps:

• Select B5:B8.
• Go to the Home tab >> click Editing >> click Find & Select >> select Replace.

• In Find and Replace, enter USA in Find what and United States in Replace with.
• Click Replace All.

• In the MsgBox, click OK.

This is the output.

Practice here.

## Related Articles

<< Go Back to Find and Replace | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF