Excel is a robust spreadsheet program that facilitates the orderly storage and management of massive datasets. Excel’s search function is a lifesaver for quickly finding a single piece of text or a certain number inside a large spreadsheet. Working with huge datasets can help users save time and energy by facilitating the rapid identification and modification of pertinent data to find and search text in excel files.

This tutorial will teach you how to search and find text in Excel files via the program’s built-in search tool and more advanced filters and macros. Whether you’re just starting with Excel or a seasoned pro, you’ll benefit from these search and analysis techniques.

Tips on how to find and search text in Excel files?

There are several ways to find and search text in Excel files. Here are some of the most commonly used methods:

Using the Find and Replace Function:

The Find and Replace function in Excel is a quick and easy way to search for a name in an Excel spreadsheet. Here’s how to use it:

 

• Press Ctrl+F or go to Home > Find & Select > Find to open the Find and Replace dialog box.
• Enter the text you want to search for in the Find What box.
• Choose any additional options, such as Match Case or Entire Cell Contents.
• Click Find Next to locate the first instance of the text in the worksheet.
• To find all instances of the text, click Find All.
• To replace the text with a new value, click Replace or Replace All.

Using Advanced Filtering:

Excel’s Advanced Filtering feature allows you to filter data based on specific criteria, including text. Here’s how to use it:

• Select the range of cells you want to search.
• Go to Data > Sort & Filter > Advanced.
• In the Advanced Filter dialog box, select the option to filter by a specific value.
• In the Criteria box, enter the text you want to search for.
• Click OK to filter the data and display only the cells that contain the specified text.

Using Macros:

If you need to search for specific text regularly, you can create a macro to automate the process. Here’s how to create a simple macro to search for text:

Press Alt+F11 to open the Visual Basic Editor. Go to Insert > Module to create a new module. In the module, enter the following code:

• Sub FindText() Dim strText As String strText = InputBox(“Enter the text to find:”) Cells.
• Find(What:=strText, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).
• Activate End Sub
• Save the macro and close the Visual Basic Editor.
• To use the macro, go to Developer > Macros, select the macro from the list, and click Run.

These are just a few of the methods for finding and searching text in Excel files. Using these tools and techniques lets you quickly and easily locate specific information within your worksheets.

Using VBA:

Applying a VBA code to search for text in multiple Excel files is reliable, particularly when dealing with many files. Unlike the File Explorer search, which sometimes fails to provide all outputs and takes more time to complete, the VBA code ensures all relevant files are searched. To apply this method, follow the steps below.

• To begin, open the worksheet where you intend to split the text.
• Launch the Microsoft Visual Basic Applications window by pressing the Alt+F11 keys in Excel.
• Select the Insert button and choose Module from the menu to create a module.
• You can write the following VBA macro in the Module window after opening a new window.

Sub SearchForValue()
Dim rngSearch As Range
Dim strSearch As String
Dim rngResult As Range
'Set the range to search
Set rngSearch = Range("A1:A10")
'Get the value to search for from user input
strSearch = InputBox("Enter the value to search for:")
'Search for the value in the range
Set rngResult = rngSearch.Find(strSearch)
'Check if the value was found
If rngResult Is Nothing Then
MsgBox "Value not found."
Else
MsgBox "Value found at " & rngResult.Address
End If
End Sub

• The search range is established at the first stage of this code (rngSearch).
• Afterward, it requests the search value from the user (strSearch). After that, it attempts to locate that value in the given range using the Find operation (rngResult).
• If the value cannot be located, a dialog window will show up explaining why. But if the value is located, a dialog box will show the location of the cell containing the value.

Utilizing File Explorer:

The application known as File Explorer is a tool for file management and searching within folders. To search for all Excel files in Windows 10/11, please refer to the following steps.

• Locate the directory where you wish to conduct the text search.
• Access the Folder Option for searching and opt for “Always search file names and contents”.
• Press the Apply button to confirm the changes.
• Next, click on the search bar located at the top right corner of the folder and enter your desired text to search.
• Once the search is completed, you can see a list of all Excel workbooks containing the searched text.

Using the selected workshop method

You can either search the full workbook or just the sheets you choose. Choose all of the tabs in the worksheet you wish to search by clicking them while holding down the Control key.

You can conduct a Find on multiple selected worksheets at once by highlighting them all first.

Consider the case when the default worksheet names of “Sheet 1,” “Sheet 2,” and “Sheet 3” are in use. Your data is spread over multiple sheets, and you need to conduct a search for the word “computer” on Sheets 1 and 3. Below are the measures you’d take to achieve this goal.

• If it is not already highlighted, click the Sheet1 tab.
• To use Ctrl, hold down the shift key.
• Hold down Ctrl and select the Sheet 3 tab.
• Release Ctrl after highlighting Sheets 1 and 3, then press Ctrl+F to bring up the Find and Replace dialog.
• Make sure the Inside box in Search and Replace is set to Sheet.
• Then, either use the Find Next or Find All option to continue your search.

Search Text in Multiple Excel Files Without Opening Them

If you have a large number of Excel files and you want to search for a specific text in them, you can use a tool called AnyTXT. It is a free and fast desktop search engine that can index and search various file formats, including Excel files.

Within AnyTXT, you can enter the text you want to search in the search box and hit enter. AnyTXT will show you a list of Excel files that contain the text, along with a preview of the content. You can double-click on any file to open it with your default Excel program.

You can also refine your search by using filters, such as file name, size, date, etc. AnyTXT is a powerful and convenient tool that can help you search inside Excel files without opening.

Conclusion

Searching for the content you’re looking for in an Excel file is usually a simple and fast process. Users may quickly find all Excel documents that contain specific content by using the search box in the folder’s upper right corner. Finding specific data in a huge collection of Excel files can be made easier with the help of this application. A user can quickly and easily locate text within an Excel file by following these easy steps.