23

I'm using Microsoft Office 2007. How can I search inside multiple Excel files?

6 Answers 6

23

With the folder that houses the files open, press Ctrl+f to use the search box in the folder. Then search for

content:"my string to search for"

to search for that string in the files in the current directory.

6
  • 3
    That appears to do a plain-text content search only xlsx files have binary data. It didn't work for me. Apr 29, 2015 at 17:56
  • 2
    Really? When I search for plaintext using this method, I get multiple .xlsx files returned, and they all do indeed seem to contain the search string I searched on, even though they do indeed contain binary data.
    – pabrams
    Apr 4, 2016 at 20:23
  • 1
    The folder must be configured for this. Right-click to see the folder's properties. Click Advanced. Click on checkbox to "Allow files... to have contents indexed..."
    – Smandoli
    May 13, 2016 at 20:30
  • 1
    Doesn't work for me in Excel 2016. It returns a lot of xlsx files but none of them contains my expected string
    – phuclv
    Feb 28, 2017 at 4:27
  • 1
    I created a test case of dummy files with an exact string I was looking for. I made xlsx, docx, and txt files with and without the string. Those with the string I noted in the file name, those without likewise. Using content search it correctly separated the two groups.
    – jxramos
    Jan 26, 2018 at 22:39
10

Use IceTeaReplacer. It's freeware.

Search & Replace in .docx, .xlsx and .xls files

IceTeaReplacer is a simple, yet a powerful tool to search inside multiple Microsoft's Office Word 2007 (docx), Excel 2007 (xlsx) and Excel 2003 (xls) files within a directory (and it's subdirectories) and replace provided phrase. Supports UTF-8.

enter image description here

There is also a more powerful alternative with various features but it's a commercial one: ExcelPipe.

2
  • content:"my string to search for" simple isn't it
    – TechDog
    Dec 26, 2016 at 5:38
  • @TechDog: These are some alternatives with "replace" functionality. Dec 30, 2016 at 11:28
9

After searching online, I found there was no one universal solution for the problem of searching multiple excel spreadsheets online, so I decided to write my own excel spreadsheet that searches other excel spreadsheets, that can be found here.

Please note it's only been tested in Excel 2010, but it should work in 2007 and earlier versions. If not, feel free to modify the code how you see fit.

The spreadsheet makes use of a custom userform and makes use of VBA code (which you're welcome to use for any purpose). Naturally, macros etc have to be enabled for this to work, and the form appears upon opening the spreadsheet (you can access the code by pressing Alt + F11, going to the userform, and double-clicking the 'Begin Search' button in the design window).

Full explanation of functions and features can be found on the Github readme, but it basically allows you to specify two text search terms to search for within a specified directory (that you can navigate to), it can search said directory recursively, and open spreadsheets that are password protected (so long as you provide the password).

It will search individual sheets in each workbook on a cell level search. Depending on workbook size, it can take roughly a second to scan each workbook.

It displays all search results in a side window, including any spreadsheets it failed to open. These results can be saved to a text file for later reference.

The userform should be relatively self-explanatory, however the readme for it on Github goes into great depth on how to use. Again, free to use, it's open-source.

2
  • Can you provide a screen shot or two? Apr 15, 2017 at 16:12
  • To do a standard "contains" search, be sure to check the "Part?" and "Case Sensitive?" check-boxes.
    – Brandon S
    Jul 11, 2018 at 16:58
1

In Excel 2003 you had an extra search option in the file menu where you were able to search through files. I can't find this option in 2007 anymore so I think it's gone.

If your have Vista or Windows 7 you should be able to use window's integrated search. In older versions you could install a search app like Windows Desktop Search, Google Desktop Search, Copernic, ... they're all free to use.

4
  • In Win 7 you can specify a type:xlsx parameter in the search box (or xls, etc. as appropriate)
    – jonsca
    Jul 21, 2011 at 11:42
  • @jonsca, OP question was about searching inside not searching for excel files.
    – Myer
    May 15, 2012 at 14:09
  • @PeterNore Yes, the comment was a supplement to If your have Vista or Windows 7 you should be able to use window's integrated search. If you specify the file type or the extension in addition to your "content:" string it doesn't have to search the entire directory, just the pertinent files.
    – jonsca
    May 15, 2012 at 16:12
  • 1
    The Windows Desktop Search link redirects, not sure if that thing still exists elsewhere. Google Desktop Search is no more. Copernic is freemium and the free version looks crippled at first sight, the comparison page doesn't even say if xls is supported in the free version. Jul 14, 2016 at 16:44
1

I created a python script (for my own needs) that allows to search for all excel files listed in a csv (csv created either manually or by an awesome must-have Everything tool)

It can search for unlimited amount of terms(search phrases) and results are exported to an excel-openable (with utf-9 names also) file here is the link, https://github.com/merof-code/search-excel it can do it in parallel (1000 files in ~150sec for me), and sort results by found keywords count.

0

Before using the CTRL+F in the folder that contains the excel files, make sure your File Explorer Search setting is set properly.

Go to the "View" tab on the File Explorer, the go to the Options-->Search, and check "Always search file names and contents (this might take several minutes)"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .