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

#### Hardware Man

##### New Member
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?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
First enter the following User Defined Function (UDF) in a standard module:

Code:
``````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)

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!

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
Code:
``````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

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!

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

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.

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

Code:
``````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``````

Replies
0
Views
220
Replies
0
Views
308
Replies
3
Views
179
Replies
7
Views
293
Replies
0
Views
527

1,203,483
Messages
6,055,675
Members
444,807
Latest member
RustyExcel

### 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.

### Which adblocker are you using?

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

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