Solved search multiple words in excel 2010

mmcconaghy 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. MIKE http://www.skeptic.com/

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.

mmcconaghy 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. . MIKE http://www.skeptic.com/

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.

mmcconaghy 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: XX 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: =OR(ISNUMBER(SEARCH(MyColors, A1))) 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. MIKE http://www.skeptic.com/

Cool, thanks I will give it a try

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.

mmcconaghy 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. MIKE http://www.skeptic.com/

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.

mmcconaghy 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. MIKE http://www.skeptic.com/

Awesome! works well. Thanks so much for your time.

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?