Search advice appreciated

[Paul Beaty]

I'd be most grateful for somec advice on Excel 2003 & a search facility. I
have an excel file with some 40,000 rows of phrases in column A - 1- 40,000.
When searching phrases with a keyword and using 'find' I can click through
phrase after phrase okay, locating those phrases with the keyword in them.
The question is - Is there a facility within Excel that allows me to search
for phrases with multiple keywords within them, and not just single
keywords. By multiple keywords I mean keywords that do not run
consecutively i.e. searching for a phrase with 'good & day' within them
which is as such - 'It has been a good long day.'

Many thanks for any advice

Paul Beaty

 

[Ed]

Hi, Paul. You might try the AutoFilter. You can use Custom, and put two
criteria in as Contains. Other than that, you might be looking at coding a
search loop: find all that have your first word and hide the rest, then
search for the second word and hide those that don't have it - rinse and
repeat until done.

HTH
Ed

 

[Paul Beaty]

Much appreciated Ed - The Auto Filter is just what I was looking for.

Paul B.

 

[Dave Peterson]

Even though you have an answer, if you have multiple phrases to look for, you
could use a helper column and put a formula like:

=countif(a1,"*this is phrase 1*")+countif(a1,"*another phrase here*")+....

Then filter on that that column for anything bigger than 0.

You could even stack them in another formula easier to use if the number of
phrases gets large:

=SUMPRODUCT(COUNTIF(A1,{"*this is phrase 1*","*another phrase here*"}))