Search for multiple strings simultaneously

**denverdale** · 03-08-2011, 02:34 AM

Anyone help me with this one?

I'm using the following formula to look in Column N and indicate TRUE if the string "at home" is any place in the column. Then I sort the column and parse out the TRUE records.

=ISNUMBER(SEARCH("at home",N2))

What I would like to do is be able to look for several strings at one time and have the formula indicate TRUE or FALSE if any one or more of the strings are present. (FALSE to display if none of the strings are present.)

Example: "at home" "from home" "home based" "opportunity"

What I'm doing is extracting ads from online directories, and then parsing out the ones that are related to working from home. There may be a few hundred words in the listing. I want have the formula check all the text and indicate TRUE if any of the chosen words or phrases are present.

I can put the formula in several columns and then sort each column. That's a PIA though. Doing all at one time would be much better.

Thanks

Dale

**canapone** · 03-08-2011, 03:05 AM

Hi, you could write a list of the strings you're searching (in the example $D$2:$D$4) and try:

=ISNUMBER(MATCH(1,SEARCH("*"&$D$2:$D$4&"*",B2)))

to be confirmed with control+shift+enter: it's an array formula.

Hope it helps.

Regards

**denverdale** · 03-08-2011, 03:10 AM

I don't understand....

Is there a way to accompolish what I want using the actual strings within quites like the original formula I used within this help request? I have tried a few and failed, leading me here for help.

Thanks

**canapone** · 03-08-2011, 03:43 AM

Hi, maybe

=ISNUMBER(MATCH(1,SEARCH("*"&{"from home","opportunity","at home";"sales"}&"*",B2)))

To be confirmed with enter.

I've suggested an external list to match, because it seemed to me more convenient if you have many strings to control.

Regards

**DonkeyOte** · 03-08-2011, 03:54 AM

Dale, welcome to the board though please note your original thread title fell foul of Forum Rules.

In short your thread titles should accurately and concisely describe your problem whilst using terms appropriate to a Google search.

Given this was your first post I have on this occasion modified on your behalf, however, I would ask that going forward you ensure all posts adhere to the aforementioned rules.