Find - multiple entries

R

Rick

When using the find function, is there a way (separators...etc) to
search for more than just one keyword or Value? I am looking for a
way to search for multiple items with a single query box. I've tried
to use Boolean operators, semicolons, commas...etc.

I'm using Excel 2000.

Am I just out of luck?

Thanks

Rick
6821065raa
 
D

Dave Peterson

Maybe in one special case....

If you're looking for Blue AND book in that order, you could use:

blue*book
 
J

Jim Cone

Possibly the commercial Excel add-in XL Companion (from yours truly).
The current issue of "The Office Letter" has some comments about it...
http://www.officeletter.com/current.html
About 1/3 of the way down the page.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Rick" <[email protected]>
wrote in message
When using the find function, is there a way (separators...etc)
to search for more than just one keyword or Value?
I am looking for away to search for multiple items with a single query box.
I've tried to use Boolean operators, semicolons, commas...etc.

I'm using Excel 2000.
Am I just out of luck?
Thanks
Rick
6821065raa
 
R

Ron Coderre

Do you want the actual locatiion of each list item in the cell to be tested?
or perhaps one of these sugestions....

With
A1: My pear tastes like an apple.

If you want how many list items are in the cell
B1: =SUMPRODUCT(COUNTIF(A1,"*"&{"apple","pear","banana"}&"*"))

In that case, B1 returns 2

OR...If you just want to know if the cell contains any of the listed items?
or
B1: =MAX(COUNTIF(A1,"*"&{"apple","pear","banana"}&"*"))>0
In this case, B1 returns TRUE

In both of those examples, you could put the list in a range of cells,
instead of embedding them in the formula.
Example:
D1: Apple
D2: Pear
D3: Banana

B1: =SUMPRODUCT(COUNTIF(A1,"*"&D1:D3&"*"))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Top