3 Easy Ways to Find and Replace in Multiple Excel Files

Every now and then we have to deal with a very large-scale dataset in our practical life. Data sets can be in different datasheets of the same workbook. They can also be entered into different workbooks as well. So if there is any mistake or if we want to change any particular cell name or something like that it becomes a tedious process to find and change them manually. But we can do this job very easily with help of some Excel tools. In that way, life becomes so much easier and we can also save a lot of time. Therefore inside this article, we will learn how to find & replace multiple excel files. We will learn three easy methods to do this effectively.

📁Download Excel File

Download Excel file from here.

Learn to Find and Replace in Multiple Excel Files in These 3 Methods

Here we are going to learn three different methods to find & replace multiple excel files. We are going to use the find & replace tool in a single excel file as well as in different excel files. We are also going to use VBA. The sample dataset with multiple datasheets is given below.

Here in this step, we are going to learn how to find & replace within different sheets of a single Excel file. So the step-by-step procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the Editing option.
• Following that we select the Find & Select option.
• Finally, we select the Find option.

• Here we will find a dialogue box.
• We write the name we want to change in Find what box.
• Then we hit Replace option.

• Firstly here we write our replacing name in Replace with.
• We select Workbook within the dropdown box.
• Now we select Replace All option.

• A dialogue box will confirm the replacements.

• Finally, we will see in cell B6 that the replacement has been made.

📕 Read More: 3 Quick Ways to Replace Text After Specific Character

In this section, we will learn to find & replace different sheets in multiple excel files. Therefore the step-by-step breakdown procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we have an Excel workbook shown below.

• Our second Excel workbook is also shown below.

• So now firstly we again go to the Find & Replace option like before.
• We fill our Find what and Replace with.
• Then we hit Replace All option.
• A dialogue box will confirm the changes made in the first workbook.
• Now we change our tab to the second workbook without closing the dialogue box.

• Here at first, we select the Find Next option.
• We then select Replace All option.
• Finally, we will see a dialogue box confirming the replacements made in the second workbook.

📕 Read More: 7 Ways to Find and Replace Multiple Values in Excel

Here in this section, we are going to discuss how to find & replace multiple excel files using embedded VBA. Hence the step-by-step procedures on how to do that are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the Developer option.
• Following that we select the Visual Basic option.

• Now we select the Insert option.
• After that, we select the Module option.

• Now we write this script in the module box.

Sub Find_Replace()
Dim Halfyearly2 As Worksheet
Dim PreviousName As Variant
Dim NewName As Variant
Dim j As Long
PreviousName = Array("Robin")
NewName = Array("Roben")
  For j = LBound(PreviousName) To UBound(PreviousName)
      For Each jSheet In ActiveWorkbook.Worksheets
        jSheet.Cells.Replace What:=PreviousName(j), Replacement:=NewName(j), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Next jSheet
  Next j
End Sub

• Then we save the module and close the dialogue box.

• Now we go to the Macros option.

• We select the Macro Name.
• We select All Open Workbooks in the Macros in the dropdown box.
• Finally, we select the Run option.

• We will find that the cell value B6 is replaced with the updated name.

📕 Read More: 13 Ways to Search for Text in Range in Excel

How to Find and Replace Multiple Values in Excel

Here in this section, we will learn how to find & replace multiple values in Excel using the REPLACE function. So the breakdown steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly in the formula box of cell D6, we write this formula and hit Enter.

• Now we use the Fill Handle tool to drag the formula.

• Finally, we will see that the cells in column D are updated.

📕 Read More: How to Find and Replace Using Wildcards in Excel

Find and Replace Multiple Words at Once in Excel

Here we will see how to find & replace multiple words at once in Excel using the SUBSTITUTE function. Therefore the step-by-step procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the formula box of cell D6 and write this formula.

• Following that we use the Fill Handle option to copy the formula.

• Finally, we will see in column D that all the words are replaced at once.

📄   Important Notes

🖊️  When we use Excel functions we have to be careful about the function argument.

🖊️  Furthermore we need to save the Excel file as Macro enabled workbook while using the VBA.

📝  Takeaways from This Article

📌  We can use the Find & Replace tool in a single Excel file.

📌  If we want we can use the same tool in different Excel files.

📌  Moreover we can apply the VBA code to find & replace multiple Excel files.

Conclusion

Here in this article, we have learned three different methods to find and replace multiple Excel files. We can do it in the sheets for a single Excel workbook. We can also find & replace multiple Excel files in different Excel workbooks. Also if we want we can use the embedded VBA option. Users can use any of these 3 methods. You can comment in our comment box for any further queries. To learn and know about other Excel-related solutions please visit our website Excelden.

Related Article

• 5 Tricks to Insert Comma Between Words in Excel