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