CODEWITHSUNDEEP
excel
user9013730
user9013730

Reputation:

Ms Excel IF ISNUMBER SEARCH for multiple text using AND or OR operator

This is my excel data

enter image description here

As you can see in B2, this formula only search for the first text it found and will ignore the rest.

What I'm trying to accomplish is if 2 or more different texts are found like B2, is it possible to print another message ... let say Apple & Banana found

enter image description here

This is my original excel formula for your reference:

=IF(ISNUMBER(SEARCH("apple",A2)),"Apple",
IF(ISNUMBER(SEARCH("banana",A2)),"Banana",
IF(ISNUMBER(SEARCH("cher",A2)),"Cherries",
"Other")))

Upvotes: 0

Views: 26163

Answers (3)

Johan
Johan

Reputation: 1

Here is my proposal :

You create a Table with your KeyWord and Result in two columns (columns K for KeyWord and L for Result).

You change your formula like this

=IF(ISNUMBER(SEARCH(K2,A2)),L2, IF(ISNUMBER(SEARCH(K3,A2)),L3, IF(ISNUMBER(SEARCH(K4,A2)),L4, "Other")))

After that, is more easy to add new KeyWord, to change the order if you want to prioritize a KeyWord more than an other.

You can't use more than 63 KeyWords, but you can create a second formula from 64 to 126.

Upvotes: 0

Alex
Alex

Reputation: 11

I have tried and tried and thought hmmm why don't I simply add a SUBSTITUTE function...and it worked ;o)

Just in case there is someone out there looking for this:

=SUBSTITUTE(TRIM(CONCATENATE(IF(ISNUMBER(SEARCH("apple",A2)),"Apple "," "),
IF(ISNUMBER(SEARCH("banana",A2)),"Banana "," "),
IF(ISNUMBER(SEARCH("cher",A2)),"Cherries "," "),
IF(SUM((ISNUMBER(SEARCH({"apple","banana","cher"},A2,1)))+0)=0,"Other "," "))),"Apple Banana","Both")

Upvotes: 1

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

See if following formula tweak helps you:

=TRIM(CONCATENATE(IF(ISNUMBER(SEARCH("apple",A2)),"Apple "," "), IF(ISNUMBER(SEARCH("banana",A2)),"Banana "," "), IF(ISNUMBER(SEARCH("cher",A2)),"Cherries "," "), IF(SUM((ISNUMBER(SEARCH({"apple","banana","cher"},A2,1)))+0)=0,"Other "," ")))&" found"

Upvotes: 0

Related Questions