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

Looking for ways to find and replace multiple words in a word from an Excel list? Then, this is the right place for you. Here, you will find 8 different ways with some explanations to do that in Excel.

How to Find and Replace Multiple Words from Excel List: 8 Ways

We can find and replace multiple words in other words from an Excel list by using different functions and VBA. Here, we have a dataset containing the names of some Cities along with their Country names in Short Form. Now, we will show you how you can find these Country Names and replace them with the corresponding Full Forms. Follow any of the methods given below to do it on your own dataset.

1. Use Nested SUBSTITUTE Function to Replace Multiple Words in Word

In the first method, we will use the Nested SUBSTITUTE function to replace multiple words in other words from an Excel list. Using this function, you can first find a text from a given cell range and then replace the text with a new text.

Here are the steps.

Steps:

• Firstly, select Cell C5 and insert the following formula.

• Secondly, press Enter.
• Thus, you can find and replace multiple words.

Read More: Find And Replace Multiple Values in Excel

2. Find and Replace Multiple Words Using XLOOKUP Function from Excel

Next, we will find and replace multiple words from an Excel list using the XLOOKUP function. However, you can use this function only in Excel 365 version. This function is used to lookup for a value in a range and then return a given value.

Steps:

• In the beginning, select Cell C5 and insert the following formula.

• After that, press Enter.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Finally, multiple words will be replaced using the VLOOKUP function.

Read More: How to Find and Replace Using Formula in Excel

3. Insert IFNA & VLOOKUP Functions for Multiple Find and Replace of Words

We can also find and replace multiple words with other words by inserting the IFNA and  VLOOKUP functions in Excel. We usually use the VLOOKUP function to look up a value in a range and the IFNA function to return a value if the function is True and a different value if it is not True.

Steps:

• Firstly, insert the following formula in Cell C5.

• After that, press Enter.
• Further, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Thus, you can find and replace multiple words using the IFNA and VLOOKUP functions.

Read More: How to Find and Replace in Excel Column

4. Create Custom LAMBDA Function to Find & Replace from Excel List

Here, you will find a way to create a custom LAMBDA function to find and replace from the Excel list. LAMBDA function is a custom function where you can give a name and formula to that function according to your choice. To do that, follow the steps given below.

Steps:

• To start with, press Ctrl +F3.
• Now, the Name Manager box will appear.
• Then, click on New.

• Next, the New Name box will open.
• After that, type any name of your choice in the Name box. Here, we will type MReplace.
• Further, insert the following formula in the Refers to box.

• Then, click on OK.

• Finally, click on Close.

• Afterward, select Cell C5 and insert the following formula.

• Lastly, press Enter and the words will be replaced.

5. Find and Replace Multiple Words Using User-Defined Function

In the fifth method, we will show you how you can find and replace words from an Excel list using a user-defined function. UDF is created using VBA when you want to use a function but can not find it in Excel.

Here are the steps.

Steps:

• Firstly, go to the Developer tab >> click on Visual Basic.

• Now, the Microsoft Visual Basic for Applications box will open.
• Then, click on Insert >> select Module.

• After that, insert the following code in your 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

• Next, click on the Save button to save the code.

• Then, select Cell C5 and insert the following formula.

• Finally, press Enter and the words will be replaced using the user-defined function.

6. Apply VBA to Replace Multiple Words from Excel List

Now, we will apply VBA to find and replace multiple words in the Excel list. Go through the steps given below to do it on your own dataset.

Steps:

• Initially, insert a module going through the same steps shown in Method 5.
• Then, insert the following code in your module.

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

• Next, save the code and go to your dataset.
• After that, go to the Developer tab >> click on Macros.

• Now, the Macro box will open.
• Then, select Country_Replace.
• Further, click on Run.

• Next, a Bulk Replace box will appear.
• Afterward, insert cell range B5:B11 in the Source List box.
• Then, click OK.

• After that, insert cell range D5:E7 in the Replace by range box.
• Lastly, click on OK.

• Finally, multiple words will be replaced by applying VBA.

Read More: How to Find and Replace within Selection in Excel

7. Find and Replace Multiple Words Using REPLACE Function

Next, we will find and replace multiple words using the REPLACE function from the Excel list. You can use this function only if you want to replace words from a list with the same order of another cell range.

Steps:

• Firstly, select Cell C5 and insert the following formula.

• Secondly, press Enter.
• Thus, you can replace multiple words using the REPLACE function.

8. Combine IF, REPLACE & LEN Functions to Find & Replace Multiple Words from Excel List

In the final method, we will combine the IF, REPLACE & LEN functions to find and replace multiple words from an Excel list.

Follow the steps given below to do that.

Steps:

• To start with, insert the following formula in Cell C5.

• After that, press Enter.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Thus, you can find and replace multiple words using the IF, REPLACE, and LEN functions.

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

Additionally, you will find a way to find & replace a single word multiple times from an Excel list using the Find & Replace feature here.

Here are the steps.

Steps:

• Firstly, select cell range B5:B8.
• Then, go to the Home tab >> click on Editing >> click on Find & Select >> select Replace.

• Now, the Find and Replace box will open.
• After that type USA in the Find what box and the United States in the Replace with box.
• Lastly, clock on Replace All.

• Next, a MsgBox will open.
• Afterward, click on OK.

• Finally, the word will be replaced multiple times using this feature.

Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Download Practice Workbook

You can download the workbook to practice yourself.

Conclusion

So, in this article, we have shown you 8 easy ways to find and replace multiple words in a word from an Excel list. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Additionally, please let us know if there are any more alternatives that we may need to look into. Thank you!

Related Articles

• How to Find and Replace Text Color in Excel
• How to Show Dash Instead of Zero in Excel
• How to Find and Replace Values in Multiple Excel Files
• How to Replace Text in Excel Formula
• Replace Text of a Cell Based on Condition in Excel

<< Go Back to Find and Replace | Learn Excel