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.

find and replace formula in excel


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.

find and replace formula in excel

  • 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))

find and replace formula in excel

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.

find and replace formula in excel

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


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)

find and replace formula in excel

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.

find and replace formula in excel

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.

find and replace formula in excel

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

find and replace formula in excel

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.

find and replace formula in excel


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

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo