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

Get FREE Advanced Excel Exercises with Solutions!

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.
`=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:B11,E5,F5),E6,F6),E7,F7)` • Secondly, press Enter.
• Thus, you can find and replace multiple words. 🔎 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.

### 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)` • 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. ### 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)` • 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: 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:

• 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.
`=LAMBDA(text,old,new, IF(old<>"", MReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))`
• Then, click on OK. • Finally, click on Close. • Afterward, select Cell C5 and insert the following formula.
`=MReplace(B5:B11,E5,F5)` • 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`````` 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. ### 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. • 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. ### 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)` • 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.

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

`=IF(LEN(B5)=2,REPLACE(B5,1,2,IF(B5="US","United States",IF(B5="SP","Spain",IF(B5="UK","United Kingdom")))),B5)` • 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. ## 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. ## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects. Arin Islam

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 Advanced Excel Exercises with Solutions PDF  