Sometimes, we need to search for text in multiple Excel files. Unfortunately, there is no direct method or formula to complete this kind of operation. In this article, I will show you two easy ways to search for text in multiple Excel workbooks in a particular folder. Hence, read carefully and save your time.
How to Search Text in Multiple Excel Files: 2 Easy Ways
Personally, I have searched for various methods to search for text in multiple Excel files. But most of them were to search text in a single Excel workbook. After all, I have found two effective methods to complete our desired task. Basically, it includes a general File Explorer search and a VBA method. For the purpose of the demonstration, we will search for the text “Sample Dataset” in multiple Excel files. Hence, one of the file’s texts is shown in the following image.
1. Utilizing File Explorer to Search Text in Multiple Files
In general, File Explorer is an application that can be used to manage files. In addition to this, it can be used to search for multiple items in a folder. Hence, follow the steps below to do the operation.
Steps:
- Firstly, navigate to the folder where you want to search for the text.
- Then, go to Search from the Folder Option and select “Always search file names and contents”.
- Now, click on the Apply button.
- After that, from the upper right corner of the folder, click on the Search bar.
- Write your text in the search bar accordingly. For the purpose of demonstration, we have searched for the text “Sample Dataset” in all the Excel workbooks.
ext:xlsx* Sample Dataset
- Finally, you will find all the Excel workbooks containing the text you have searched for.
Similar Readings:
- Find Text in Excel Range and Return Cell Reference
- How to Find Multiple Values in Excel
- How to Find First Value Greater Than in Excel
- How to Find First Occurrence of a Value in a Range in Excel
2. Applying VBA to Search Text in Multiple Excel Files
You can also apply a VBA code to search for text in multiple Excel files. It is the best method because the File Explorer search sometimes fails to provide all outputs. Moreover, File Explorer search takes more time when the number of files is huge. However, go through the following steps to apply for a VBA to complete the task.
Steps:
- Firstly, open the worksheet where you want the text to be split.
- Secondly, hold the Alt+F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
- Thirdly, click the Insert button.
- After that, click on Module from the menu to create a module.
- A new window will open. Hence, write the following VBA macro in the Module window.
Sub SearchFolders()
'ExcelDemy Publications
'Declaring Variables
Dim xDgl As Object
Dim xFld As Object
Dim xBnkSearch As String
Dim xBnuPath As String
Dim xGnuPath As String
Dim xHty As Worksheet
Dim xTn As Workbook
Dim xRw As Worksheet
Dim xVoi As Long
Dim xGenerate As Range
Dim xGbrPosition As String
Dim xNewBox As FileDialog
Dim xNew As Boolean
Dim xCalculate As Long
Dim xJTQ As Workbook
Dim xJTQStrPath As String
Dim xBln As Boolean
Set xJTQ = ActiveWorkbook
xJTQStrPath = xJTQ.Path & "\" & xJTQ.Name
On Error GoTo FaultService
Set xNewBox = Application.FileDialog(msoFileDialogFolderPicker)
xNewBox.AllowMultiSelect = False
xNewBox.Title = "Select a Folder"
If xNewBox.Show = -1 Then
xBnuPath = xNewBox.SelectedItems(1)
End If
If xBnuPath = "" Then Exit Sub
xBnkSearch = "Sample Dataset"
xNew = Application.ScreenUpdating
Application.ScreenUpdating = False
Set xHty = Worksheets.Add
xVoi = 4
With xHty
.Cells(xVoi, 2) = "Workbook"
.Cells(xVoi, 3) = "Worksheet"
.Cells(xVoi, 4) = "Cell"
.Cells(xVoi, 5) = "Text in Cell"
Set xDgl = CreateObject("Scripting.FileSystemObject")
Set xFld = xDgl.GetFolder(xBnuPath)
xGnuPath = Dir(xBnuPath & "\*.xls*")
Do While xGnuPath <> ""
xBln = False
If (xBnuPath & "\" & xGnuPath) = xJTQStrPath Then
xBln = True
Set xTn = xJTQ
Else
Set xTn = Workbooks.Open(Filename:=xBnuPath & "\" & xGnuPath, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
End If
For Each xRw In xTn.Worksheets
If xBln And (xRw.Name = .Name) Then
Else
Set xGenerate = xRw.UsedRange.Find(xBnkSearch)
If Not xGenerate Is Nothing Then
xGbrPosition = xGenerate.Address
End If
Do
If xGenerate Is Nothing Then
Exit Do
Else
xCalculate = xCalculate + 1
xVoi = xVoi + 1
.Cells(xVoi, 2) = xTn.Name
.Cells(xVoi, 3) = xRw.Name
.Cells(xVoi, 4) = xGenerate.Address
.Cells(xVoi, 5) = xGenerate.Value
End If
Set xGenerate = xRw.Cells.FindNext(After:=xGenerate)
Loop While xGbrPosition <> xGenerate.Address
End If
Next
If Not xBln Then
xTn.Close (False)
End If
xGnuPath = Dir
Loop
.Columns("A:D").EntireColumn.AutoFit
End With
MsgBox xCalculate & " cells have been found", , "ExcelDemy for Excel"
ExitHandler:
Set xHty = Nothing
Set xRw = Nothing
Set xTn = Nothing
Set xFld = Nothing
Set xDgl = Nothing
Application.ScreenUpdating = xNew
Exit Sub
FaultService:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
VBA Code Breakdown
- Firstly, we create a new procedure Sub in the worksheet using the below statement
Sub SearchFolders()
- Secondly, we declare variables as
'Declaring Variables
Dim xDgl As Object
Dim xFld As Object
Dim xBnkSearch As String
Dim xBnuPath As String
Dim xGnuPath As String
Dim xHty As Worksheet
Dim xTn As Workbook
Dim xRw As Worksheet
Dim xVoi As Long
Dim xGenerate As Range
Dim xGbrPosition As String
Dim xNewBox As FileDialog
Dim xNew As Boolean
Dim xCalculate As Long
Dim xJTQ As Workbook
Dim xJTQStrPath As String
Dim xBln As Boolean
- Thirdly, we activate the VBA sheet and set xJTQ, xNewBox to open a dialog box.
Set xJTQ = ActiveWorkbook
xJTQStrPath = xJTQ.Path & "\" & xJTQ.Name
On Error GoTo FaultService
Set xNewBox = Application.FileDialog(msoFileDialogFolderPicker)
xNewBox.AllowMultiSelect = False
xNewBox.Title = "Select a Folder"
- Now, we applied two if loops. Write the text you want to search for in the xBnuSearch.
If xNewBox.Show = -1 Then
xBnuPath = xNewBox.SelectedItems(1)
End If
If xBnuPath = "" Then Exit Sub
xBnkSearch = "Sample Dataset"
xNew = Application.ScreenUpdating
Application.ScreenUpdating = False
- Again, we set xHty, xDgl, xFld, xTn.
Set xHty = Worksheets.Add
xVoi = 4
With xHty
.Cells(xVoi, 2) = "Workbook"
.Cells(xVoi, 3) = "Worksheet"
.Cells(xVoi, 4) = "Cell"
.Cells(xVoi, 5) = "Text in Cell"
Set xDgl = CreateObject("Scripting.FileSystemObject")
Set xFld = xDgl.GetFolder(xBnuPath)
xGnuPath = Dir(xBnuPath & "\*.xls*")
Do While xGnuPath <> ""
xBln = False
If (xBnuPath & "\" & xGnuPath) = xJTQStrPath Then
xBln = True
Set xTn = xJTQ
Else
Set xTn = Workbooks.Open(Filename:=xBnuPath & "\" & xGnuPath, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
- After that, we started an END If function and activated the cells according to the strings to get the result.
End If
For Each xRw In xTn.Worksheets
If xBln And (xRw.Name = .Name) Then
Else
Set xGenerate = xRw.UsedRange.Find(xBnkSearch)
If Not xGenerate Is Nothing Then
xGbrPosition = xGenerate.Address
End If
Do
If xGenerate Is Nothing Then
Exit Do
Else
xCalculate = xCalculate + 1
xVoi = xVoi + 1
.Cells(xVoi, 2) = xTn.Name
.Cells(xVoi, 3) = xRw.Name
.Cells(xVoi, 4) = xGenerate.Address
.Cells(xVoi, 5) = xGenerate.Value
End If
Set xGenerate = xRw.Cells.FindNext(After:=xGenerate)
Loop While xGbrPosition <> xGenerate.Address
End If
- Finally, we end and exit the Sub of the VBA macro as
Next
If Not xBln Then
xTn.Close (False)
End If
xGnuPath = Dir
Loop
.Columns("A:D").EntireColumn.AutoFit
End With
MsgBox xCalculate & " cells have been found", , "ExcelDemy for Excel"
ExitHandler:
Set xHty = Nothing
Set xRw = Nothing
Set xTn = Nothing
Set xFld = Nothing
Set xDgl = Nothing
Application.ScreenUpdating = xNew
Exit Sub
FaultService:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
- Lastly, press the F5 key to run the VBA code a dialog box named Select a Folder will appear.
- Accordingly, select the folder where you want to search for and press OK.
- In addition to this, another dialog box will appear showing the number of cells found. Now, press OK.
- Finally, you will find the name of the workbook, sheet number, and even cell number of the searched text as in the following image.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These are all the steps you can follow in Excel to search text in multiple Excel files. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
Related Articles
- How to Find Last Cell with Value in a Row in Excel
- How to Use Excel Formula to Find Last Row Number with Data
- How to Find Last Row with a Specific Value in Excel
- How to Find Last Non Blank Cell in Row in Excel
- How to Find Last Cell with Value in Column in Excel
- Find Last Value in Column Greater than Zero in Excel
- Excel Find Last Column With Data
- [Solved!] CTRL+F Not Working in Excel