CONCATENATE search question

W

wonderd

Board Regular
Joined
Oct 20, 2013
Messages
168
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
Code: 
=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
Code: 
=CONCATENATE(IF(ISNUMBER(SEARCH("PHRASE" OR "PHRASE 2",H2)),"43",""))

Is this possible?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Try
Code: 
=IF(OR(H2="PHRASE",H2="PHRASE 2"),43,"")
 
Upvote 0
Nope, is there a way to make this work?
Code: 
=CONCATENATE(IF(ISNUMBER(SEARCH("shag","flokati",H2)),"4",""))
 
Upvote 0
Try
Code: 
=IF(COUNT(FIND({"PHRASE","PHRASE 2"},H2)),"43","")
 
Upvote 0
Small problem. It does not work if the word is in the cell but its not the same capitalization. For example "phrase" will not output 43 unless its "PHRASE"

Also I am using this formula back to back like
Code: 
[COLOR=#333333]=IF(COUNT(FIND({"PHRASE","PHRASE 2"},H2)),"43","")&[/COLOR][COLOR=#333333]IF(COUNT(FIND({"PHRASE 3","PHRASE 4"},H2)),"50","")[/COLOR]

Is there a way to have the outputs separated by comma? I tries using ",50" but if this is the only output then the comma can cause a problem.
 
Last edited:
Upvote 0
Edit. I solved the first problem by using
Code: 
CONCATENATE(IF(COUNT(SEARCH({"phrase1","phrase 2"},H2)),",40",""))

Still trying to find a comma separation solution
 
Upvote 0
Replace FIND with SEARCH
I,m also guessing PHRASE is only a substitute word for the real thing....try this
Code: 
=IF(COUNT(SEARCH({"PHRASE 1","PHRASE 2"},H2)),"43",IF(COUNT(SEARCH({"PHRASE 3","PHRASE 4"},H2)),"50",""))
 
Upvote 0
If I use FIND then "Phrase" will not output anything unless its "PHRASE" so the code below works
Code: 
[COLOR=#333333][COLOR=#333333]=IF(COUNT(FIND({"PHRASE","PHRASE 2"},H2)),"43","")&[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]IF(COUNT(FIND({"PHRASE 3","PHRASE 4"},H2)),"50","")[/COLOR][/COLOR]

But the comma solution did not work even with using FIND, also yes phrase is just a substitute.
 
Last edited:
Upvote 0
The formula in Post #8, provides the solution that...

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 none of the results are correct in H2 the cell will be BLANK

Isn't that what you wanted ....:confused:
Note the change in formula
Rich (BB code): 
=IF(COUNT(SEARCH({"PHRASE1","PHRASE 2"},H2)),"43",IF(COUNT(SEARCH({"PHRASE 3","PHRASE 4"},H2)),"50",""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Lookup value based on multiple criteria where one is searching a cell for specific text string
Replies
0
Views
259
alex0182828
A
M
  • Question
Condense formula
Replies
8
Views
372
SanjayGMusafir
SanjayGMusafir
D
  • Question
Find and replace function
Replies
9
Views
1K
Akuini
Akuini
M
  • Solved
VBA Search Multiple value to find match in Cell string
Replies
7
Views
2K
Peter_SSs
Peter_SSs
J
  • Question
If And formula not working
Replies
7
Views
236
KRice
K

Forum statistics

Threads
1,214,915
Messages
6,122,212
Members
449,074
Latest member
cancansova

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top