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

[Hardware Man]

I have a problem I simply can't get my head around.

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 some products have more than one. When there are multiple collections, they are separated with ///.

Columns B and C are easy. They simply contain the products color and brand name, respectively.

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

Obviously, when there are more "collection names" the final answer gets longer. Can someone here help me come up with a formula that will handle this?

 

[Gary's Student]

First enter the following User Defined Function (UDF) in a standard module:

Public Function Combiner(R As Range) As String
ary = Split(R(1).Value, "///")
If UBound(ary) = 0 Then
    Combiner = ary(0) & "-" & R(2).Value & "-" & R(3).Value
    Exit Function
End If
For L = LBound(ary) To UBound(ary)
    Combiner = Combiner & ary(L) & "-" & R(2).Value & "-" & R(3).Value & ","
Next L
Combiner = Left(Combiner, Len(Combiner) - 1)
End Function

User Defined Functions (UDFs) are very easy to install and use:


1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window


If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx


To remove the UDF:


1. bring up the VBE window as above
2. clear the code out
3. close the VBE window


To use the UDF from Excel:


=combiner(A1:C1)


To learn more about macros in general, see:


Getting Started with Macros and User Defined Functions


or


Writing Your Own Functions In VBA


for specifics on UDFs


Macros must be enabled for this to work!

 

[ZAX]

Welcome On Board Hardware Man,

These Are Two Functions Made With VBA.Insert them in the codes window and then type the IF function below
the

Function CountLet(Str, Letter As String)
n = 0
For i = 1 To Len(Str)
If Mid(Str, i, 1) = Letter Then
n = n + 1
End If
Next
CountLet = n
End Function

Function Element(Text, Sep, Order)
i = Split(Text, Sep)
Element = i(Order - 1)
End Function

Enter This Formula In The First Cell Of Column D And Drag It Down, But This Formula Only Works With 3 Collections For a Product.

=IF(CountLet(A1,"/")=0,A1&"-"&B1&"-"&C1,IF(CountLet(A1,"/")=1,Element(A1,"/",1)&"-"&B1&"-"&C1&","&Element(A1,"/",2)&"-"&B1&"-"&C1,IF(CountLet(A1,"/")=2,Element(A1,"/",1)&"-"&B1&"-"&C1&","&Element(A1,"/",2)&"-"&B1&"-"&C1&","&Element(A1,"/",3)&"-"&B1&"-"&C1,IF(CountLet(A1,"/")=3,Element(A1,"/",1)&"-"&B1&"-"&C1&","&Element(A1,"/",2)&"-"&B1&"-"&C1&","&Element(A1,"/",3)&"-"&B1&"-"&C1&","&Element(A1,"/",4)&"-"&B1&"-"&C1))))

ZAX

 

[Hardware Man]

That was elegant it should have been illegal!

I wanted to make the description of the problem as simple as possible and just used columns A:D to describe it. In reality, the fields were scattered across a pretty large worksheet. I moved things around to get it to work, but for future reference, how would I call for 3 different columns if they weren't already set up in a range?

Thanks again! I gotta start looking into VBE!

 

[Hardware Man]

ZAX,

I came here to thank the poster above you and just noticed your reply. I'll probably stick with the first one since it's already done, but I wanted to thank you for your input!

Michael

 

[Rick Rothstein]

I wanted to make the description of the problem as simple as possible and just used columns A:D to describe it.

It is almost always a bad idea to simplify your problem for us as it usually gets you great answers to a question you do not really have nor care about.

 

[Gary's Student]

Using three distinct cells rather than a block of three is a very minor change:

Public Function Combiner(R1 As Range, R2 As Range, R3 As Range) As String
ary = Split(R1.Value, "///")
If UBound(ary) = 0 Then
    Combiner = ary(0) & "-" & R2.Value & "-" & R3.Value
    Exit Function
End If
For L = LBound(ary) To UBound(ary)
    Combiner = Combiner & ary(L) & "-" & R2.Value & "-" & R3.Value & ","
Next L
Combiner = Left(Combiner, Len(Combiner) - 1)
End Function