Search formula for multi occurences

  • Thread starter Thread starter bkanealy
  • Start date Start date
B

bkanealy

All,

I'm trying to write a formula that will return a string of data t
Column A if it is in Column B. I have no problem getting one instanc
to work, but if I try to search for let's say Apples, Oranges I can'
figure out how to get search to look for both.

I've tried working this out through IF function, but there's got to b
an easier way. TIA.

Bria
 
Frank,

I'm sorry, I forgot to mention the text is imbedded. So, the fiel
would read "red apples", "juicy oranges", but I also have a multitue o
other variants where the text is in the middle of the strin
unfortunately.

Bria
 
Hi Brian,

try,

=IF((ISNUMBER(FIND("apples",B1)))+(ISNUMBER(FIND("oranges",B1))),"Found",
"Not Found")

Note that the FIND function is case sensitive. You can use SEARCH
instead if you don't want it case sensitive.

Hope this helps!
 
Hi
some ideas:
1. one way
=IF(OR(ISNUMBER(FIND("apple",A1)),ISNUMBER(FIND("orange",A1))),"Found",
"not found")

2. another way
=IF(SUMPRODUCT(COUNTIF(A1,"*" & {"orange","apple"} & "*")),"found","not
found")
 
Frank,

Thank you so very much. I took the first formula, essed it up a bit
but it appears to work just fine. Thank you again. Here's what I ende
up with.

Brian

=IF(ISNUMBER(SEARCH("apples",E1)),MID(E1,SEARCH("apples",E1),6),IF(ISNUMBER(SEARCH("orang",E1)),MID(E1,SEARCH("orang",E1),5),"other")
 
Back
Top