Foo - SEARCH function in Excel.

I'm trying to use the search function to search for two strings in text. That is, I have a spreadsheet whose "L" column is populated with short phrases, like "I ate a rotten orange and mango today." It could be anything. The point is I want to use the SEARCH function to find entries that mention mango OR orange. I thought I'd use something like
=SEARCH(OR("mango", "orange")$L$1:$L$250) but it isn't working. How can I adapt the SEARCH function to search for multiple strings?

I'm sure there's a better way, but you could search for 'mango', then 'orange', and sum the results. What is your desired output format?

Try the = Find(find_text,within_text,start_num) function.

I don't think the search function is designed to search a range. I think this is the job of a macro.

I would just shove a fruit bat into the CD rom drive.

Try the = Find(find_text,within_text,start_num) function.

Find is has the same declaration as Search pretty much.
I don't believe that within_text is allowed to be a range.
It's supposed to be either a string value or a single cellreference. the 3rd parameter is the indexto start looking for the pattern after.

Find is the same thing as Search except that it's case sensitive. My desired output is to sort through the ~250 entires with mango or orange mentioned and produce a new column indicating where this occurs.

http://support.microsoft.com/kb/282151
The macro Code in the 2nd box in (Workaround) section is similar to what you want.

this person wrote a FindAll VBA macro/function that is reusable.
http://www.cpearson.com/excel/FindAll.aspx

Find is has the same declaration as Search pretty much.
I don't believe that within_text is allowed to be a range.
It's supposed to be either a string value or a single cellreference. the 3rd parameter is the indexto start looking for the pattern after.

this is correct. An easy way around this is to place the string to be looked for at the top of the column, and use absolute references or semi absolute references in order to search for multiple items. Filtering for results or even using a pivot table to summarize results.

Of course, if you know access and VBA, one can simply load sql results into a result set and then search those result sets.

try a combination formula like =Search("mango")AND("orange") - using whatever syntax excel uses. I use Lotus 123, where I would use a complex "IF" statement in a new column. That might work in excel.

I would just shove a fruit bat into the CD rom drive.

Kind of messy, don't you think? I'd open the case and put it in that way.

Kind of messy, don't you think? I'd open the case and put it in that way.

I should have prefaced my post with the fact that I am not a techno geek. You can open the case? Really?

Kind of messy, don't you think? I'd open the case and put it in that way.


I should have prefaced my post with the fact that I am not a techno geek. You can open the case? Really?


So, if its an apple, you're making fruit salad? where do the raisins go?

You can run Excel on an apple? ON me, the raisins go straight through, so I am going to answer "toilet".

Toilet, apple.....same thing.

I should have prefaced my post with the fact that I am not a techno geek. You can open the case? Really?

Yes, but do it carefully so as not to interrupt the gnomes working in there. Also, don't disconnect the tubes if you like Internet access.

But the real question is: Why is he looking for oranges and mangoes in his spreadsheet? They're at the store (or on a tree)!

try a combination formula like =Search("mango")AND("orange") - using whatever syntax excel uses. I use Lotus 123, where I would use a complex "IF" statement in a new column. That might work in excel.

That's what I was thinking - and IF statement.

Toilet, apple.....same thing.
You know, one make much better pies than the other.

You know, one make much better pies than the other.

mud pies.....apple pies......what's your point?