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.

**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

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

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

- 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!

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