Combining the Contents of 3 cells into 1 (with a twist)

[Michael D]

This forum has helped me enormously but I have a problem I simply can't get my head around. The attached workbook has sample data showing what I have and where I need to get (as well as some notes explaining the problem), but I'll try to explain it here as well...

In column A I have some text data which contains the "collections" that a typical product belongs to. Usually there is just a single collection, but there can be more than one. When there are multiple collections, they are separated with a ///.

columns B and C are easy. They simply contain the products color and brand name.

What I need is a formula in column D that will combine the contents of the other 3 columns, and put a dash between the elements. When there is just a single "collection" name, it's easy. I just concatenate the 3 fields together and put a dash between each one, so a typical result might read:

CollectionA-Red-CompanyABC

But if there are X collections, I'd need to create X results in column D and separate them with a comma. So a typical result if there were 2 collections for a product would be:

CollectionA-Red-CompanyABC,CollectionB-Red-CompanyABC

Sorry if the above seems confusing, but if you just download the attached sample file I think it should be pretty clear.

Thanks in advance!excel sample.xlsx

[majosum]

Took me like a half hour to solve this. Take a look at the red font in the Excel document I attached. The formula is intense. Let me know if you need me to change anything else around.

[Michael D]

Awesome! It worked like a charm! Thanks!!!!!!