Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

You can download the workbook to practice yourself.


8 Ways to Find and Replace Multiple Words in Word from Excel List

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

Ways to Find and Replace Multiple Words in Word from Excel List


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.

Use Nested SUBSTITUTE Function to Replace Multiple Words in Word from Excel List

Here are the steps.

Steps:

  • Firstly, select Cell C5 and insert the following formula.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:B11,E5,F5),E6,F6),E7,F7)

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

Find and Replace Multiple Words in Word from Excel List

🔎 How Does the Formula Work?

  • Here, in the formula, we set cell range B5:B11 as the text range.
  • Then, in the first SUBSTITUTE function, we substituted the value the same as Cell E5 with the value of Cell F5.
  • Similarly, we substituted Cell E6 with Cell F6 and Cell E7 with Cell F7.

Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)


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.
=XLOOKUP(B5, $E$5:$E$7, $F$5:$F$7, B5)

Find and Replace Multiple Words Using XLOOKUP Function from Excel List

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

In the XLOOKUP function, we inserted Cell B5 as lookup_value, cell range E5:E7 as lookup_array, cell range F5:F7 as return_array, and Cell B5 as if_not_found.
  • Finally, multiple words will be replaced using the VLOOKUP function.

Read More: How to Find and Replace Using Formula in Excel (4 Examples)


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.
=IFNA(VLOOKUP(B5, $E$5:$F$7, 2, FALSE), B5)

Insert IFNA & VLOOKUP Functions for Multiple Find and Replace of Words from Excel List

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

🔎 How Does the Formula Work?

  • Firstly, in the VLOOKUP function, we looked for the value of Cell B5 in cell range E5:F7, inserted 2 as col_index_num and FALSE as range_lookup as we wanted to find an Exact match.
  • Then, we set this resultant as the value in the IFNA function and Cell B5 as value_if_na.
  • Thus, you can find and replace multiple words using the IFNA and VLOOKUP functions.

Read More: [Fixed!] Excel Find and Replace Not Working (6 Solutions)


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.

Opening Name Manager Box to to Find and Replace Multiple Words in Word from Excel List

  • 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.
=LAMBDA(text,old,new, IF(old<>"", MReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))
  • Then, click on OK.

Opening New Name Box to Find and Replace Multiple Words in Word from Excel List

  • Finally, click on Close.

  • Afterward, select Cell C5 and insert the following formula.
=MReplace(B5:B11,E5,F5)

Create Custom LAMBDA Function to Find & Replace Multiple Words in Word from Excel List

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

Read More: How to Find and Replace Multiple Words at Once in Excel (7 Methods)


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.

Find and Replace Multiple Words Using User-Defined Function from Excel List

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

Opening Microsoft Visual Basic for Applications Box to Find and Replace Multiple Words in Word from Excel List

  • 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

Code Breakdown

  • In the beginning, we created a user-defined function as MultiwordReplace.
  • Then, we declared the variables.
  • Next, we counted the source rows, input columns and found_rows.
  • Again, we redeclared variables.
  • After that, we used For loops to replace the values.
  • Next, click on the Save button to save the code.

  • Then, select Cell C5 and insert the following formula.
=MultiWordReplace(B5:B11,E5:E7,F5:F7)
  • Finally, press Enter and the words will be replaced using the user-defined function.

Read More: How to Find and Replace Multiple Values in Excel with VBA (3 Examples)


Similar Readings


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

Code Breakdown

  • Firstly, we created a Sub Procedure named Country_Replace.
  • Then, we declared range_1 as Range, Sourcerange_1 & Replacerange_1 as Range.
  • Next, we set an InputBox as Sourcerange_1.
  • After that, we checked If Sourcerange_1 is nothing, then we added another InputBox as Sourcerange_1.
  • Further, we checked If Replacerange_1 is nothing, then we set ScreenUpdating as False.
  • Finally, we used a loop for each range_1 in Replacerange_1 to replace the value of range_1.
  • 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.

Opening Macro Box to Find and Replace Multiple Words in Word from Excel List

  • 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 Methods)


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.
=REPLACE(B5:B9,1,2,F5:F9)

Find and Replace Multiple Words in Word Using REPLACE Function from Excel List

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

Here, in the REPLACE function, we inserted cell range B5:B9 as old_text, 1 as start_num, 2 as num_chars and cell range F5:F9 as new_text.

Read More: Excel VBA to Find and Replace Text in a Column (2 Examples)


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.
=IF(LEN(B5)=2,REPLACE(B5,1,2,IF(B5="US","United States",IF(B5="SP","Spain",IF(B5="UK","United Kingdom")))),B5)

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

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

🔎 How Does the Formula Work?

  • Firstly, we used a Nested-IF function to change the value of Cell B5 if it is equal to “US”, “SP” or “UK”.
  • Then, in the REPLACE function we inserted Cell B5 as old_text, 1 as start_num, 2 as num_chars and the result of the Nested-IF function as new_text.
  • Finally, we used another IF function where we checked if the length of the text in Cell B5 is equal to 2 using the LEN function. If this function is True, it will return the resultant value of the REPLACE function, or else it will return the value of Cell B5.
  • Thus, you can find and replace multiple words using the IF, REPLACE, and LEN functions.

Read More: Find And Replace Multiple Values in Excel (6 Quick Methods)


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.

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

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

Opening Find and Replace Box to to Find and Replace from Excel List

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

Practice Section


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. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo