How to Find and Replace Using Formula in Excel (4 Examples)

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.

 How to Find and Replace Using Formula in Excel: 4 Ways

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.

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: Find and Replace Tab Character in Excel

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.

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 Replace Text in Excel Formula

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.

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.

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.

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.

Read More: How to Find and Replace Multiple Words from a List in Excel

Practice Section

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

Download Practice Workbook

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

• How to Find and Replace Text Color in Excel
• How to Show Dash Instead of Zero in Excel
• Replace Text of a Cell Based on Condition in Excel
• How to Find and Replace within Selection in Excel
• How to Find and Replace Values in Multiple Excel Files

<< Go Back to Find and Replace | Learn Excel