This article will help you to understand the **find and replace formula(s) **in Excel. Sometimes we need to edit our data in Excel sheets and for that purpose, we need to** replace words with other words or alphabets.** There are various ways to use **find and replace formula(s) **to edit data in an Excel sheet. I’ll be explaining some easiest possible ways to do this in this article.

We are going to work on the following dataset where we have put the **name **of some famous **movies **and corresponding **lead actors**.

**Table of Contents**hide

## Download Practice Workbook

## 4 Ways to Find and Replace Using Formula in Excel

### 1. Using Excel FIND and REPLACE Functions to Find and Replace Character

Using **FIND** and **REPLACE** functions is the best way to find and replace any character in an Excel dataset. Here we are going to replace the first **name **of the **lead actors **with its first alphabet. Let’s see how we can do this.

**Steps:**

- First, make a new
**column**for the**short form**of the**actors’**names and type the following formula in cell**D5**.

`=REPLACE(C5,1,FIND(" ",C5),LEFT(C5,1)&". ")`

Here, the **REPLACE **function takes the cell reference **C5**, starts counting the characters until it finds a **space **in it with the help of the **FIND **function, and then replaces the first **name **with its starting alphabet and a **dot (.) **with the help of **LEFT** function.

- Hit the
**ENTER**button. After that, you will see the output in cell**D5**.

- Now use the
**Fill Handle**to**AutoFill**the lower cells. You will see all the**actors’**names start with their corresponding first**alphabet**and a**dot**.

Thus you can replace some characters in a string with the desired character by using **find and replace formula(s) **in Excel.

**Read More:** **How to Replace Special Characters in Excel (6 Ways)**

### 2. Applying SUBSTITUTE Function to Find and Replace Character in Excel

We can also replace the first **name **of the **lead actors **with their corresponding **alphabet **by using the **SUBSTITUTE **function**. **Let’s discuss the necessary steps for this method below.

**Steps: **

- Make a new
**column**for the**actors’ short names**and type the following formula in cell**D5**.

`=SUBSTITUTE(C5,C5,LEFT(C5,1)&". ") &RIGHT(C5,LEN(C5)-FIND(" ",C5))`

**Formula Breakdown**

Here we have nested **LEFT**, **RIGHT**, **LEN****, **and **FIND** functions in the **SUBSTITUTE **function to replace the first name of the **lead actors **with their corresponding first **alphabet**.

**LEN(C5) —->**The**LEN**function returns the**numbers**of the characters in cell**C5**.**Output: 14**

**FIND(” “,C5) —->**Returns the**position**of the**space**in cell**C5**.**Output: 10**

**RIGHT(C5,LEN(C5)-FIND(” “,C5)) —->**becomes**RIGHT(C5,14-10) —->**turns into**RIGHT(C5,4)****Output: Bale**

**LEFT(C5,1)&”. ” —->**becomes**C & “.”****Output: C.**

**SUBSTITUTE(C5,C5,LEFT(C5,1)&”. “) &RIGHT(C5,LEN(C5)-FIND(” “,C5)) —->**Reduced to**SUBSTITUTE(C5,C5, “C. Bale”)****Output: C. Bale**

Finally, we get the first **alphabet **of the first **name** in cell **C5 **with a dot.

- Now, hit the
**ENTER**button to see the output in cell**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can replace some characters in a string with the desired character by using **find and replace formula(s) **(in this case, it was the **SUBSTITUTE **function) in Excel.

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

**Similar Readings**

**How to Find and Replace Values in Multiple Excel Files (3 Methods)****How to Find And Replace Values Using Wildcards in Excel****Data clean-up techniques in Excel: Adding text to cells****How to Find and Replace within Selection in Excel (7 Methods)****Excel VBA: How to Find and Replace Text in Word Document**

### 3. Find and Replace Cells Using Excel XLOOKUP Function

The **XLOOKUP **function can also be used as a **find and replace **function in Excel. Suppose, we used short names for some **movies **but after that, we want to replace their short **names **with their original **names**. For instance, the original name of the movie **Batman 1 **was **Batman Begins**. So we want to replace **Batman 1 **with **Batman Begins**. Let’s discuss the procedure in the following description.

**Steps:**

- We made some modifications in our dataset. We changed some
**movie names**and made a new**column**for the full name of those**movies**. - Then we created the list of the
**movies**which we will replace with their original**names**.

- Type the following formula in cell
**D5**.

`=XLOOKUP(B5,$F$5:$F$7,$G$5:$G$7,B5)`

Here,** $F$5:$F$7** and **$G$5:$G$7 **are the **lookup_array** and the **return_array **respectively. If the value from the **B5 cell** matches with the **lookup_array**, then the **return_array **returns the corresponding value. If we can’t find the value, then the **cell** value will be returned. Value from **cell** B5 “**Batman 1**” is found on the lookup array, therefore, we’ll get “**Batman Begins**”.

- Press the
**ENTER**button so that you can see the output in cell**D5**.

- Now use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the **names **of the **movies in short **replaced with their original **names**. This is another method you can use to **find and replace **a particular string with a new string.

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

### 4. Utilizing Excel VLOOKUP Function to Find and Replace Characters

We also can apply the **VLOOKUP **function to **find** characters of a string and **replace **it with another. Suppose, we mentioned some **movies **with their serial number but after that we want to replace them with their original **names**. For instance, the original name of the movie **Batman 1 **was **Batman Begins**. So we want to replace **Batman 1 **with **Batman Begins**. Let’s discuss the procedure in the following description.

**Steps:**

- We made some changes to our dataset. We changed some
**movie names**and made a new**column**for the full name of those**movies**. - Then we created the list of the
**movies**which we will replace with their original**names**.

- Type this formula in cell
**D5**.

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

Here, we use the **VLOOKUP** function to replace words. If any value is not found, then an error will be shown. That’s why, with the help of the **IFERROR** function, we can replace any error with the corresponding cell value. However, our lookup array has two **columns **and the second **column **will return our desired **output**. 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.

- Now press the
**ENTER**button and you will see the output in cell**D5**.

- After that, just use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the original **names **of the **movies **that you wanted to put over their corresponding **short names**. Thus, the use of the **VLOOKUP **function helps us to **find and replace **particular strings with new strings.

**Related Content:** **How to Substitute Multiple Characters in Excel (6 Ways)**

## Practice Section

Here I have given you the dataset that I used to explain these examples. You can practice these examples on your own.

## Conclusion

In this article, I have shown some combined use of **find and replace formula(s) **in Excel. I tried to explain the examples as simple as possible. I hope you may be benefited from this article. If you have any other easy methods or ideas or any feedback regarding these examples, please leave them in the comment box. This will help me enrich my upcoming articles.

**Related Articles**

**Add Text and Formula in the Same Cell in Excel (4 Examples)****How to Find and Replace Multiple Values in Excel with VBA (3 Examples)****[Fixed!] Excel Find and Replace Not Working (6 Solutions)****How to Find and Replace in Excel Column (6 Ways)****How to Find and Replace Asterisk (*) Character in Excel****Data clean-up techniques in Excel: Adding text to cells**