CONCATENATE comma question

[wonderd]

Hello I am using this formaula

=CONCATENATE(IF(COUNT(SEARCH({"phrase 1","phrase 2"},H2)),"40",""))&IF(COUNT(SEARCH({"phrase 3","phrase 4"},H2)),"50","")

Is there a way for me to separate the output by a comma without using a ",50"

If the only output is phrase 3 or phrase 4 then ,50 will cause a problem. I am looking for if both output then 40,50 and if only the second one then 50

 

[dermie_72]

you could try:

=CONCATENATE(IF(COUNT(SEARCH({"phrase 1","phrase 2"},H2)),"40",""))&IF(COUNT(SEARCH({"phrase 3","phrase 4"},H2)),",","50","")

Not sure if that is what you're after, but it tested ok at this end

 

[martindwilson]

=SUBSTITUTE(TRIM(IF(COUNT(SEARCH({"phrase 1","phrase 2"},H2)),"40","")&" "&IF(COUNT(SEARCH({"phrase 3","phrase 4"},H2)),"50",""))," ",",")

 

[Michael M]

Clever Martin..ray:

I'll add a link to the other post, so the OP doesn't get caught for double posting

Same / similar question here

http://www.mrexcel.com/forum/excel-questions/785849-concatenate-search-question.html

 

[wonderd]

Martins formula works but how would it look if I wanted to add multiple search phrases?

=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","")

Like this but with different phrases and outputs?

 

[wonderd]

I will end up using the above formula then running

= MID(I2, (LEFT(I2)=",") + 1, LEN(I2))

Thanks for the help, the thread wasn't a total loss....I wouldn't of came up with the above without the help found here and the other thread....

 

[wonderd]

If anyones wondering this is the finished formula

=CONCATENATE(IF(COUNT(SEARCH("modern",H2)),"modern","")&IF(COUNT(SEARCH("contemporary",H2)),",contemporary","")&IF(COUNT(SEARCH({"oriental","traditional"},H2)),",oriental","")&IF(COUNT(SEARCH({"shag","flokati"},H2)),",shag","")&IF(COUNT(SEARCH("braided",H2)),",braided","")&IF(COUNT(SEARCH("outdoor",H2)),",outdoor","")&IF(COUNT(SEARCH("kid",H2)),",kids","")&IF(COUNT(SEARCH("natural",H2)),",natural","")&IF(COUNT(SEARCH("southwest",H2)),",southwest","")&IF(COUNT(SEARCH({"solid","striped"},H2)),",solid striped","")&IF(COUNT(SEARCH("floral",H2)),",floral","")&IF(COUNT(SEARCH("animal",H2)),",animal","")&IF(COUNT(SEARCH("transitional",H2)),",transitional",""))

Its used for converting attributes of an area rug manufactures excel database to attributes accepted by the website so the products get categorized correctly when uploaded.

I will be creating different versions of this formula for different attributes (weave, color, material)

 

[Michael M]

I still think a UDF or code would have been a far better solution !!

 

[wonderd]

You could be right. How does that work? Is it inserted into the cell like a formula or does it have to be ran like a macro?

 

[Michael M]

No, it's a type of macro......but anyone writing it would have to have ALL the details, and necessary criteria, not dummy data !!