Searching data within an Excel cell.

September 2, 2009 7:15 AM

Excel2007Filter: I want to be able to search information between two things in a single cell in Excel. So a cell may have 4-9 typed in it, but I want that cell to be highlighted if a user searches for 7. Or 4. Or anything between 4 and 9 inclusively. (More details and a screenshot of the spreadsheet inside.)

This is for running Excel 2007 on Windows XP, in case it matters.

I have a spreadsheet with 127 rows and 97 columns. Just about each cell has some sort of range of data in it.

Here's some examples of the data:
just numbers (1-26)
combinations (3263 #141-170)letters and numbers (A. A45 #1323-1349)
dates (E 203 Oct 16, 1991-Nov 30, 1992)
letters and numbers (E1411-E1415)
words and numbers (C. 2906 Units 59-64)

Ideally, what I want to have happen is that there will be a set of cells that will be used for searching. The user would type in the data they have (example: E 203 November 1, 1991), the cell with that information would light up.

I'm pretty sure that I'll have to have different search cells that will have to be filled in different ways in order to get all the information searchable. So there would be a cell for the category (E), a cell for the series (203), and a cell for the date (November 1, 1991).

The problem is, I'm not sure how to get Excel to recognize the information in the middle. I'm sure searching for Oct 16, 1991 will make the cell light up, but I want to get the information in between Oct 16, 1991-Nov 30, 1992 to be picked up by the searching as well.

Here's a screenshot of part of the spreadsheet for more detail.

I've got a passing familiarity with Excel and formulas, but I have zero knowledge of VBA.

Please help me Mefi!

posted by sperose to computers & internet (3 comments total)

I think the best approach will be try to pre-process the sheet into another sheet that can be more easily used for searching.

Basically, the new sheet would be computed using formulae to split each source cell into the low value and the high value as two cells in the new sheet.

For more complex combinations, you will need more than two cells in the new sheet.

Then, your searching is really a matter of using the Excel Data Filter command appropriately. It's much better than searching.

If you have Excel 2007, and if you format the data as a "table", then Filtering becomes more obvious because Excel automatically sets the filtering option to "on" for the table.

I do not see a simpler way of doing this. Although I'm certainly eager to see what others have to say.
posted by blue_wardrobe at 10:19 AM on September 2

I don't think you have to use a different sheet, but you do need to separate the "ranges" into two columns, one for the lowest value and one for the highest. Then you can use Conditional Formatting to make them "light up".

So if you have your search data in B2 and 4-9 in cell A5, you want 4 in Cell B5 and 9 in cell C5. Then you want this formula in cell D5: "=IF(B15<=B12,1,0)+IF(C15>=B12,1,0)". That will make cell D5 = 2 when the value is in range.

Now you use Conditional Formatting on cell A5, using "Formula is..." =$D$5>1 and change the Pattern to whatever color you will use to make it light up. You can just bold the test or change the border, however you want.

But in the cases where you have multiple things in a cell, you will have to change things so that all of the items match, so you might need more than two =If statements. You'll have to do lots of testing to make sure they work. After you have all of that filled out, you can just make the text in those cells white so that the user doesn't see your formulas, but be sure to save a good copy for yourself in case it gets messed up.
posted by soelo at 11:01 AM on September 2

check into using vlookup too. I have found it to be very helpful. for the final item in that formula, play with true vs false. One will find an approximate match, the other only an exact. I don't have time to check now, but will try to get back to this later.
posted by midwestguy at 12:04 PM on September 2