CONCATENATE search question

[wonderd]

Hello all, I am trying to create a Concatenate formula that searches a cell for "PHRASE" or "PHRASE 2". If either phrase exist than the output is 43. Can someone show me what this formula would look like? It cant be two different if statements like

=CONCATENATE(IF(ISNUMBER(SEARCH("PHRASE",H2)),"43","")&IF(ISNUMBER(SEARCH("PHRASE 2",H2)),",43",""))

This will give me 43,43

I am looking for something like

=CONCATENATE(IF(ISNUMBER(SEARCH("PHRASE" OR "PHRASE 2",H2)),"43",""))

Is this possible?

 

[wonderd]

I am looking for

1. if H2 contains either Phrase 1 or phrase 2 in any case format the answer is 43
2. if H2 contains either Phrase 3 or phrase 4 in any case format the answer is 50
3. if H2 contains BOTH Phrase 1 or phrase 2 and phrase 3 or phrase 4 in any case format the answer is 43,50


Edited*******

 

[wonderd]

Not possible?

 

[Michael M]

I'm kinda guessing you'll need to do it with a UDF

 

[wonderd]

Is there a formula that removes the first value of a cell if its a comma? I can run this on the output cells.

 

[XOR LX]

Hi,

=LOOKUP(SUM(MMULT(IFERROR(SEARCH({"Phrase1","Phrase2";"Phrase3","Phrase4"},H2)^0,0),{1;1})*{3;7}),{0,3,6,7,10,14},{"",43,43,50,"43,50",50})

Regards

Edit: changed to Phrase1, Phrase2, etc.

 

[Michael M]

example ?

 

[wonderd]

example ?

If the output is ,40,50 then the formula would make 40,50

 

[wonderd]

Hi,

=LOOKUP(SUM(MMULT(IFERROR(SEARCH("Phrase"&{1,2;3,4},H2)^0,0),{1;1})*{3;7}),{0,3,6,7,10,14},{"",43,43,50,"43,50",50})

Regards

Sorry but the phrase is just an example and will not have actual numbers. Like

[COLOR=#333333][COLOR=#333333]=IF(COUNT(FIND({"blue","black"},H2)),"43","")&[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]IF(COUNT(FIND({"green","purple"},H2)),"50","")[/COLOR][/COLOR]

 

[XOR LX]

Hi,

Yes - just realised that and amended my formula in that post.

Regards

 

[wonderd]

Thanks but honestly its to complicates for me to keep track of. The formula I am going to be using will be very long and I wont be able to keep track of what values I need to change. My formula will look like this but with different phrases and outputs

=CONCATENATE(IF(COUNT(SEARCH({"blue","black"},H2)),",4",""))&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")&IF(COUNT(SEARCH({"purple","green"},H2)),",4","")

So if there is a formula to delete the first value if its a comma then I will just have to run that after the above formula.