search function

**VegasL** · 09-04-2008, 11:44 PM

hello,

please see attached spreadsheet, what i would like for it to do is

search column a for any word/phrase that contains "dog", if it does display number next to , i.e. the one in column b, if when searching for "dog" or any variation where "dog" isnt contained in phrase, display a blank

desired result is in column f and column g.

thanks

**davesexcel** · 09-05-2008, 12:05 AM

Hi,
Try this code,

Please Login or Register  to view this content.

Attached is a sample of it in use....

**protonLeah** · 09-05-2008, 12:05 AM

1) =IF(ISERR(SEARCH("dog",A2,1)),"",A2), copies the phrase
2) =IF(ISERR(SEARCH("dog",A2,1)),"",B2), copies the adj. value

**VegasL** · 09-07-2008, 07:16 PM

Originally Posted by protonLeah

1) =IF(ISERR(SEARCH("dog",A2,1)),"",A2), copies the phrase
2) =IF(ISERR(SEARCH("dog",A2,1)),"",B2), copies the adj. value

thanks worked perfectly. also thanks davesexcel, apprecciate ur help, the simpler formula makes more sense to me

would iserror also not work?

lastly, if i want to include in the search and or "cats", would it be..? getting some kind of error...

=IF(ISERRor(and(or(SEARCH("dog","cats",A2,1)),"",A2)

**martindwilson** · 09-07-2008, 07:28 PM

just add the same thing for cats

=IF(ISERR(SEARCH("dog",A2,1)),"",A2)&IF(ISERR(SEARCH("cat",A2,1)),"",A2)

=IF(ISERR(SEARCH("dog",A2,1)),"",B2)&IF(ISERR(SEARCH("cat",A2,1)),"",B2)
dragged down will return dogs and cats
and yes
iserror will work

**VBA Noob** · 09-08-2008, 02:37 AM

If you have more crtieria then try

=IF(SUM(COUNTIF(A2,{"cat","dog"}))>0,A2,"")

VBA Noob

**VegasL** · 09-12-2008, 04:18 PM

=IF(SUM(COUNTIF(A2,{"cat","dog"}))>0,A2,"") returned empty for all cells.

i may have up to 10 criteria i.e. cat, dog, chicken, hen , etc..

**VBA Noob** · 09-12-2008, 04:22 PM

Does A2 have the condition. If anywhere in the string try

=IF(SUM(COUNTIF(A2,{"*cat*","*dog*"}))>0,A2,"")

VBA Noob

**martindwilson** · 09-12-2008, 04:24 PM

=IF(SUM(COUNTIF(A2,{"cat","dog"}))>0,A2,"") returned empty for all cells.
that coz its looking for exact match of "dog" ,"cat"
you need search so it finds the string
ah ah i see a wild card function above didn't know you could do that

**VegasL** · 09-14-2008, 02:18 PM

Thanks VBANoob. =IF(SUM(COUNTIF(A2,{"*cat*","*dog*"}))>0,A2,"") worked perfectly, appreciate your help.

I wasn't aware of countif applied to Text as well, thought it only applied to Numbers.

I'm understanding it as, Countif (lookfor "*cat", or "*dog*") within cell a2, only if those specific values exist, because (sum>0 (not sure why/why sum>0), else display cell a2, otherwise display "".

Could you translate above equation?, I'm sure I'm not reading it right?