Often we need to **replace** values. In this article, weâ€™ve shown you 7 methods to **find** and **replace** **multiple** **words** at once in **Excel**. A company previously noted its office locations in short forms, now the company wants to note those in full forms.

Weâ€™ll solve this problem using a sample dataset and it has **three columns**: **Office#**, **Office Name**, and **Office Name Full**.

**Table of Contents**hide

**Download Practice Workbook**

**7 Ways to Find and Replace Multiple Words at Once in Excel**

**1. Using Find & Replace Command to Replace Multiple Words in Excel**

For the first method, weâ€™ll use the **Replace** command of **Excel **to** replace **all the â€ś**AL**â€ť with â€ś**Alabama**â€ť.

**Steps:**

- Firstly, select
**cell**range**C5:C10**. - Secondly, from
**Find & Select**>>> select**Replaceâ€¦**

**Find and Replace dialog box** will appear.

- Thirdly, type the following things:
**Find what:****AL**.**Replace with:****Alabama**.

- Then, put a tick mark on:
**Match case**.**Match entire cell contents**.

- After that, select
**Replace All**.

A **dialog box** will appear.

- Click
**OK**. - Finally, press
**Close**.

Weâ€™ve **found** and **replaced** **two** â€ś**AL**â€ť into â€ś**Alabama**â€ť.

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

**2. Using Nested SUBSTITUTE Function to Find and Replace Multiple Words at Once in Excel**

Weâ€™re going to use the **SUBSTITUTE** function to** replace multiple words** at once.

**Steps:**

- Firstly, select
**cell**range**D5:D10**. - Secondly, type the following formula.

`=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C$5:$C$10,"AL","Alabama"),"IL","Illinois"),"MS","Mississippi")`

**Formula Breakdown**

**SUBSTITUTE($C$5:$C$10,â€ťALâ€ť,â€ťAlabamaâ€ť)****Output:****{â€śAlabamaâ€ť;â€ťCaliforniaâ€ť;â€ťDelawareâ€ť;â€ťILâ€ť;â€ťTexasâ€ť;â€ťMSâ€ť}**.- The
**SUBSTITUTE**function replaces a string with another string. Weâ€™re replacing â€ś**AL**â€ť with â€śAlabama here.

**SUBSTITUTE(SUBSTITUTE($C$5:$C$10,â€ťALâ€ť,â€ťAlabamaâ€ť),â€ťILâ€ť,â€ťIllinoisâ€ť)**- Weâ€™re replacing â€ś
**IL**â€ť , with â€ś**Illinois**â€ť.

- Weâ€™re replacing â€ś
- Finally,
**SUBSTITUTE({â€śAlabamaâ€ť;â€ťCaliforniaâ€ť;â€ťDelawareâ€ť;â€ťIllinoisâ€ť;â€ťTexasâ€ť;â€ťMSâ€ť},â€ťMSâ€ť,â€ťMississippiâ€ť)**- Weâ€™re replacing â€ś
**MS**â€ť with â€ś**Mississippi**â€ť.

- Weâ€™re replacing â€ś

- Thirdly, press
**CTRL**+**ENTER**.

Thus, weâ€™ve shown the **second** method to **find** and **replace multiple words at once**.

**Read More: How to Use the Substitute Function in Excel VBA (3 Examples)**

**3. Applying the XLOOKUP Function to Find and Replace Multiple Words at Once in Excel**

In this method, weâ€™ll use the **XLOOKUP** function to **replace multiple words**. Remember, this function is only available in **Excel 365** and **Office 2021** versions.

**Steps:**

- Firstly, type the following formula in
**cell D5**.

`=XLOOKUP(C5,$F$5:$F$9,$G$5:$G$9,C5)`

The **XLOOKUP** function returns values from a matched array.

Here,** $F$5:$F$9** is our **lookup_array**, and **$G$5:$G$9** is our **return_array**. If the value from the **C5 cell** is found on the **lookup_array**, then a value will be returned from the **return_array**. If we canâ€™t find the value, then the **cell** value will be returned. Value from **cell** **C5** â€ś**AL**â€ť is found on the lookup array, therefore, weâ€™ll get â€ś**Alabama**â€ť.

- Secondly, press
**ENTER**.

Here, the word â€ś**AL**â€ť is matched with the **lookup_array**, after that, â€ś**Alabama**â€ť is obtained from the **return_array**. Our formula needs another step. We need to put this formula to the other **cells**. Weâ€™re **replacing** the **words** in mainly **2** steps.

- Finally, we can use the
**Fill Handle**to**AutoFill**formula.

In conclusion, the **third** method works flawlessly to **replace multiple words** at once.

**Read More:** **How to Replace Text in Excel Formula (7 Easy Ways)**

**Similar Readings:**

**How to Find and Replace Asterisk (*) Character in Excel****Replace Special Characters in Excel (6 Ways)****How to Find And Replace Values Using Wildcards in Excel****Data clean-up techniques in Excel: Adding text to cells****Find And Replace Multiple Values in Excel (6 Quick Methods)**

**4. Utilizing the VLOOKUP Function to Find and Replace Multiple Words at Once in Excel**

In this method, weâ€™re going to use the** VLOOKUP** function and the **IFERROR** function to replace multiple words.

**Steps:**

- Firstly, type the formula from below in
**cell D5**.

`=IFERROR(VLOOKUP(C5,$F$5:$G$7,2,FALSE),C5)Â `

Weâ€™re using the **VLOOKUP** function to **replace words**. If any value is not found, then an error will be shown. Therefore, with the help of the **IFERROR** function, weâ€™re **replacing** any error with the corresponding **cell** value.

Moreover, our lookup array has **two columns**, we want to return from the **second column**. Therefore, we used **2 **in the formula. We want an exact match, hence we choose** FALSE** in the formula.

**Note:** Remember to use an **absolute cell reference**. Here, we need **one** extra step to apply the formula to the other **cells**.

- Secondly, press
**ENTER**.

Weâ€™ve changed â€ś**AL**â€ť to â€ś**Alabama**â€ť.

- Finally, use the
**Fill Handle**to**AutoFill**the formula.

Weâ€™ve **found** and **replaced** the words in **Excel**.

**Read More:** **How to Find and Replace Values in Multiple Excel Files (3 Methods)**

**5. Applying the REPLACE Function to Find and Replace Multiple Words at Once**

Here, weâ€™ll use the **REPLACE** function to find and **replace multiple words**.

- Firstly, select
**cell**range**D5:D8**. - Secondly, type the following formula.

`=REPLACE($C$5:$C$8,1,2,$F$5:$F$8)`

Weâ€™ve selected our **cell** range **C5:C8** as our **old_text**. The starting location to search in our string is set to **1**. We want to replace **2** characters, hence, weâ€™ve picked **2**. Finally, weâ€™ve selected **F5:F8** as our **new_text**.

- Thirdly, press
**CTRL**+**ENTER**.

**Note:** It is optional to use an **absolute** **cell reference** in this case. Our **old_text** and **new_text** need to be of the same length, else weâ€™ll get errors.

Weâ€™ve **replaced** **multiple words** at once in yet another way.

**Related Content:** **How to Find and Replace from List with Macro in Excel (5 Examples)**

**6. Combining the IF and REPLACE Functions to Find and Replace Multiple Words at Once in ExcelÂ **

We can use the **IF** function, the **LEN** function, and the **REPLACE** function to **replace** multiple words.

**Steps:**

- Firstly, type the formula from below to
**cell D5**.

`=IF(LEN(C5)=2,REPLACE(C5,1,2,IF(C5="AL","Alabama",IF(C5="IL","Illinois",IF(C5="MS","Missisippi")))),C5)`

**Formula Breakdown**

See method **5**, to understand the syntax of the **REPLACE** function.

**LEN(C5)=2**- The
**LEN**function returns the string length of a**cell**. Our short form consists of**2**characters. Hence, weâ€™re checking if it is equal to**2**. If it is**TRUE**, then weâ€™ll replace it with the**REPLACE**function. On the other hand, if it is not**TRUE**then the**cell**value will be returned.

- The

For the value in **cell C5**. We can see its length is** 2**. Hence, it will execute the **REPLACE** function. This **IF(C5=â€ťALâ€ť,â€ťAlabamaâ€ť **part will change â€ś**AL**â€ť to â€ś**Alabama**â€ť.

- Secondly, press
**ENTER**.

We can see the value is changed in **cell D5**.

- Finally,
**AutoFill**the formula.

This method is similar to method **5**. However, we donâ€™t need another helper table to do our operation. Moreover, this method requires one extra step to replace the values.

**Read More:** **Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)**

**7. Creating a Custom Function Using VBA to Find and Replace Multiple Words at Once in ExcelÂ **

For the last method, weâ€™re going to create a user-defined function using **VBA** to **replace multiple words**.

**Steps:**

- Firstly, press
**ALT**+**F11**to bring up the**Visual Basic****window**. Alternatively, you can do it from the**Developer**tab >>>**Visual Basic**. - Secondly, from
**Insert**>>> select**Module**.

- Thirdly,
**copy**and**paste**the following code.

```
Function ReplaceWords(sInput As String, rFind As Range, rReplace As Range) As String
Dim sTemp As String
Dim sFind As String
Dim sReplace As String
Dim cFind As Range
Dim lcFind As Long
Dim lrFind As Long
Dim lrReplace As Long
Dim lcReplace As Long
lcFind = rFind.Columns.Count
lrFind = rFind.Rows.Count
lcReplace = rFind.Columns.Count
lrReplace = rFind.Rows.Count
sTemp = sInput
If Not ((lcFind = lcReplace) And (lrFind = lrReplace)) Then
Â Â Â Â ReplaceWords = CVErr(xlErrNA)
Â Â Â Â Exit Function
End If
For Each cFind In rFind
Â Â Â Â sFind = cFind.Value
Â Â Â Â sReplace = rReplace(cFind.Row - rFind.Row + 1, cFind.Column - rFind.Column + 1).Value
Â Â Â Â sTemp = Replace(sTemp, sFind, sReplace)
Next cFind
ReplaceWords = sTemp
End Function
```

Weâ€™re creating a custom function named â€ś**ReplaceWords**â€ť. This function has** three** components: **sInput **as **String**, **rFind**, and **rReplace **as **Range**. Then, weâ€™re setting **7** variables. After that, weâ€™re counting the **rows** and **columns **using the **Count **method.

Here, using the **IF NOT **statement to checkÂ if the column lengths and row lengths are not equal then an error will pop up. To show that error, Weâ€™re using the **CVErr** function here. **CVErr(xlErrNA) **will return the **#NA** error.

After that, weâ€™re using a **For** loop. For each value in the **rFind** range, weâ€™re replacing it with the **sReplace** values.

**Save**the code and go back to your worksheet.

- After that, select the
**cell**range**D5:D10**and type the following formula.

`=ReplaceWords(C5,$F$5:$F$7,$G$5:$G$7)`

Weâ€™re replacing the value from **cell C5**. The values to be replaced are in the **F5:F7** **cell** range. The new values are in the **G5:G7** cell range.

- Finally, press
**CTRL**+**ENTER**.

Weâ€™ve changed the values using a custom **VBA** formula.

**Read More:** **Excel VBA: How to Find and Replace Text in Word Document**

**Things to Remember**

- Use the
**absolute cell references**where it is necessary. - Method
**3**,**4**,**6**requires one more extra step to**replace multiple words**. - The
**XLOOKUP**function is only available on**Excel 365**, and**Office 2021**.

**Practice Section**

Weâ€™ve added a practice dataset for each method in the **Excel** file. You can try our method and understand more clearly with those.

**Conclusion**

Weâ€™ve shown you **7** methods to **find** and **replace multiple words** at once in **Excel**. If you have any problems regarding these methods, feel free to comment below for assistance. Thanks for reading, keep excelling!

**Related Articles**

**How to Substitute Multiple Characters in Excel (6 Ways)****Excel VBA to Find and Replace Text in a Column (2 Examples)****[Fixed!] Excel Find and Replace Not Working (6 Solutions)****Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)****How to Find and Replace Using Formula in Excel (4 Examples)**