Solved search multiple words in excel 2010

March 12, 2013 at 07:27:56
Specs: Windows 7
I need to seach a column, or an entire worksheet, for more than one term. For example, I want to find "red" or "blue". Why is this so difficult?

Filtering doesn't help because there are 100's of cells in the column with different terms. If I filtered, I would have to scan the filter selections manually, which wouldn't be any easier than looking at the document.

Would it help that the mulitiple terms are the same for each doc? Thanks!

See More: search multiple words in excel 2010

March 12, 2013 at 15:42:29
Why is this so difficult?

Because Excel was designed for numerical calculations,
it's TEXT functions are limited.

there are 100's of cells in the column with different terms.

Do you mean that each cell has multiple text strings, IE: The Red Hat
or does each cell have only one text string, IE: Red

If you simply wish to find the text, then use the Find command on the Task Bar.

Just use the Find All button and it will display a list of all of the occurrences, with the cell location.


Report •

March 12, 2013 at 16:27:59
Some of the cells contain two words. I need to search for "red" and "blue hat" and "green" and "green hills"

Thanks for your input.

Report •

March 12, 2013 at 17:14:42
✔ Best Answer
You could also use Conditional Formatting to highlight those cells with your target words in them.

If you have 2007,

1) Select your cell or range of cells
2) On the ribbon click Conditional Formatting
3) Select Highlight Cells Rules
4) Select Text That Contains
5) In the input Box enter your text string, IE: Red
6) Click OK

All the cells in your selected range, that contain the word Red should
now be highlighted

You could also change the color for each word, so the text string Red
shows up one color, the text string Blue another.



Report •

Related Solutions

March 13, 2013 at 13:11:26
Conditional Formatting worked for me! Thanks. I made a "New Rule" for each term I wanted to highlight. I have about 20 terms I look for in multiple documents. I created the formatting in the "original" document, and saved the document. Then I could copy a cell from that doc, open up a new document and "paste special" > "Formatting" to the entire worksheet and the conditional formatting is applied.

It's not perfect but works well. I still don't know why excel's CTRL-F search will not allow you to put search several terms at once, i.e. "red" or "blue" or "green", etc. That should be a very easy to do??

Thanks again.

Report •

March 13, 2013 at 16:39:44
I made a "New Rule" for each term I wanted to highlight. I have about 20 terms

You do not need 20 Rules,
there is a way, using a Defined Name, to use only one Rule.
But it limits you to only One Color also.

If you just want to Highlight the cells, and you don't really mind using only one color then try this:

First you will need to Define a Name:

Somewhere out of the way on your spread sheet, like column XX enter your 20 target words so you have a list like:

1) red
2) blue
3) green
4) yellow

etc. etc.

19) fuchsia
20) scarlet

Now, Highlight your list of words.
Next, on the ribbon, select the Formula Tab
Click on the Define Names button
In the Name box enter a descriptive name, like MyColors
Leave the Scope on Workbook
In the Refers To: box at the bottom, be sure it is referencing your list of words.
It should look like: =Sheet1!$XX$1:$XX$20

Now we will need to define the Conditional Formatting:

1) Select your cell or range of cells: IE: A1:Z100
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:


6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

All the cells with your various target words should change to the pretty color you selected.

The advantage to this, is that you can now change or add words to your list
without having to define a new Rule in CF.

Make sure you choose a color that you have not used in your other CF Rules, or you can Delete the other Rules before you apply the above.


Report •

March 14, 2013 at 08:41:37
Cool, thanks I will give it a try

Report •

March 19, 2013 at 08:08:36
One more question: can I sort by color of cell? Ex. there are 1000 rows on a sheet with10 cells in column A that are highlighted yellow (background color). I want to put those 10 in rows 1-10. Thanks.

Report •

March 19, 2013 at 08:54:16
In 2007 & 2010, there is an option in Sort & Filter to sort on color.

On the Ribbon,
Select the Data tab
Click on Sort

In the popup window,
the middle selection titled: Sort On
allows you to sort on: Values, Cell Color, Font Color or Cell Icon.


Report •

March 19, 2013 at 10:08:14
Wow, that is extremely helpful! Thanks so much.

As long as I'm asking, is there a way to apply conditional formatting to more that one sheet at a time?

Right now, after I copy an empty cell on the formatted sheet, I select the new sheet, "paste special" > Formatting. But I do that for each sheet and it could be more efficient if I could o it to the multiple sheets at once.

Report •

March 19, 2013 at 10:19:08
To do it for a whole workbook:

Select the 1st sheet tab
Shift+Click on the last sheet tab
Select the cells you want to conditional format
do your CF on the one sheet
and the CF should now apply to all the sheets.


Report •

March 19, 2013 at 10:33:18
Awesome! works well. Thanks so much for your time.

Report •

April 1, 2013 at 10:35:08
It may be a dump question, but could you please explain me more on your formula: =OR(ISNUMBER(SEARCH(MyColors, A1))). I used only search(MyColors, A1) and then it is returned only the first term, instead of going down to other terms.

Additionally, how could I create a function to delede those rows after the search?

Report •

Ask Question