searching for a file

March 22, 2007 1:32 PM

Is there a way to find an excel file on your hard drive that contains a certain sequnce of digits in adjacent cells?

Like a file that has "6876" in one cell, and "100" in the next?

posted by goethean to computers & internet (8 comments total)

Windows? Use

Search >
Documents (word processing, spreadsheet, etc) >
Use advanced search options.
posted by iconomy at 1:55 PM on March 22, 2007

I haven't tried it before, but the trial version of this looks promising. You might want to investigate the product a little further before installing, just in case.
posted by malaprohibita at 1:56 PM on March 22, 2007

Oh, I misread your question, goethean. You could use either "6876" or "100" but I'm not sure you could use both. Try it and see.
posted by iconomy at 1:57 PM on March 22, 2007

I'm not sure if you need to find, say, that document and not all the other documents that have 6876 in them, or if you just need to find 6876 but think it might be easier if you also knew the data that was in the adjacent cell.

I'm not sure how to do the latter, but the former can totally be done. It's pretty important to know what sort of computer you have (PC? Mac?). With a Mac, Spotlight will search through the contents of a file automatically so if you type 6876 in the box you'll find it. Alternately, you can get a little app like easyfind to make this simpler.

On a PC, you'd probably want to do a field search where you search for excel files, then you limit them to ones that have 6876 in them. There are also little apps that can do sort of what you're looking for, but they're not free. The text on that page says that excel saves data in its own little proprietary format which makes hunting through it a little more challenging.

Sorry this isn't more helpful, it's sort of a convoluted way of saying "I'm not sure" but I'm also not sure exactly what you need to do.
posted by jessamyn at 2:05 PM on March 22, 2007 [1 favorite]

I don't think there's a single search that'd do that, and it doesn't appear that you can even search for two seperate values in Windows simultaneously.
1. I'd search for excel files containing 6876 to narrow the number of files you have to dig into.
2. For each file within those search results, use SORT in the DATA menu to sort by the column containing the 6876 value (which you'll find using CTRL F), and then by the adjacent column in order to complete the search visually.
posted by Oddly at 2:15 PM on March 22, 2007

You might be able to find a utility that will batch convert a bunch of Excel files into CSV files. From there, it should be pretty easy to search for 6876, 100.

Also, when I open an excel file (created in Mac v. 11.3) with a hex editor, I find this: 00 47 04 36 38 37 36 00 00 29 52 03 31 30 30 where I put in a sample 6876 in once cell and then 100 in an adjacent cell. I don't know how consistent that is, but if there's a hex editor out there that will allow you to search across multiple files, that may be an option.
posted by willnot at 2:45 PM on March 22, 2007

I'm using a PC and Win XP.
posted by goethean at 3:24 PM on March 22, 2007

I assume that you have many files with 6876 and 100 in them, so that separate searches for those numbers are not useful. To automatically find any files containing 6876 and 100 in adjacent cells is trickier, but not impossible.

When you want to do something a little more complicated than usual, so that the required functionality is not explicitly built into Excel (or another Office program), you can usually do it with Office VBA. Tools -> Macro -> Macros... -> "my search" -> Create. VBA provides functions for opening and manipulating the contents of spreadsheets, documents, etc, and you can use the Application.FileSearch object to search for all Excel workbooks on your computer (or in a folder in which you know the desired file would reside).

A VBA macro that solves your problem will be short. If you're not comfortable programming VBA, try Googling for a couple of examples or, failing that, post a further query in this thread.

If you're not comfortable programming at all, then you'll need to get someone else to do it for you, or pay for an advanced searching tool if a suitable one exists.

You could probably get someone else to do it by posting a few more details here, like: are the Excel files in which you want to search all to be found in the same directory? I would guess many other members of MetaFilter could quickly program this search in VBA. Modulo the clarifying details, I've almost programmed one out of curiosity while writing this response.
posted by hAndrew at 6:09 PM on March 22, 2007