How to Search Text in Multiple Excel Files (2 Easy Ways)

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.

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.

Search Text in Multiple Files.xlsm

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.

<< Go Back to Find Value in Range | Excel Range | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Search Text in Multiple Excel Files (2 Easy Ways)

How to Search Text in Multiple Excel Files: 2 Easy Ways

1. Utilizing File Explorer to Search Text in Multiple Files

2. Applying VBA to Search Text in Multiple Excel Files

Conclusion

Related Articles

Leave a reply Cancel reply