concatenate question

[VSCarter]

I'll try to make this understandable!

I have a spreadsheet that lists items that are included in a 'basket'. Some 'baskets' have up to 36 items in it.

I need the items listed in ONE cell. Here is what I have done so far.

I used the concatenate command to attach each item together, separating them with a line break (html) because they will appear on a web page. I had to split this into 2 commands, and then have a 3rd that combined the 2 that were just created (since it exceeded the 30 limit).

Some of the baskets only contain 3 items, but the way I have written the formula, puts a break for each item even if it's nothing --

How can I tell the original formula to only include the item if one exists in the cell? In other words, if a cell is empty, enter nothing...

Does that make sense?

Thanks!

 

[Jonmo1]

You can sidestep the 30 limitation by using a different method...

=CONCATENATE(A1,B1,C1)

Same as

=A1&B1&C1

this method is only limited by the number of characters allowed in a formula, not 30 arguments...

 

[VSCarter]

I thought I should post a snippet of the code to give an example:

=CONCATENATE(bella_basket_sub!AA2&"
"&bella_basket_sub!AB2&"
"&bella_basket_sub!AC2&"
"&bella_basket_sub!AD2&"
")

If AA2 is the ONLY cell with information in it, how can I get it to end there?

Thanks.

edit: Yikes...it's using my HTML in the code, lol ... after each there is a "<br />" that needs to be in the final cell ...

 

[Scott Huish]

This is very easy with a UDF:

Function CContact(r As Range) As String
Dim c As Range, t As String
For Each c In r
    If c <> "" Then t = t & c & vbLf
Next
If Len(t) > 1 Then CContact = Left(t, Len(t) - 1)
End Function

To put this code in, copy the code above.
Right click on one of your sheet tabs, click on View Code.
This takes you into the Visual Basic Editor.
From the menu on top, click on Insert, then Module.
Paste into white area.
Hit Alt-q

Then in the worksheet, you can do like this:
=CContact(A1:A36)

 

[Scott Huish]

I wasn't sure if you wanted a line feed or the html code < br > without the extra spaces, this can be easily changed if necessary

 

[VSCarter]

I'm not sure what's what I'm after, but of course, I could be wrong.

Let me explain it this way.

In a spreadsheet, I have listed the individual items that will go into a basket. As mentioned previously, some baskets have 36 items, some have 4.

Each column in this worksheet contain single items as such: (not rows, but for this purpose I am listing as rows)

lollipop
bicycle
wagon
telephone

In a different spreadsheet, I have a description cell that will combine all of the items that are included in this basket. But because it will be used on a webpage, I need to format this list so that it has linebreaks at the end of each item. After using the concatenate function, it would appear as such:

lollipop< br / >
bicycle< br / >
wagon< br / >
telephone< br / >
(I realize there are spaces in the break but I wasn't sure how to post this without it executing the html!)


Right now, my concatenate formula combines EVERYTHING -- 1st cell through the 36th cell. How can I tell it to only combine a cell if a value exists within it?

 

[VSCarter]

Surely there is a command I can enter into that formula that says "if a cell is blank, then skip..."

Isn't there?

 

[Scott Huish]

Yes, but I couldn't get it all to happen in one cell, which the UDF can do.

=IF(bella_basket_sub!AA2="","",bella_basket_sub!AA2&"< br >")&IF(bella_basket_sub!AB2="","",bella_basket_sub!AB2&"< br >")
etc.
Remove the extra spaces in the br tags in either the formula or code.

Revised UDF:

Function CConcat(r As Range) As String
Dim c As Range, t As String
For Each c In r
    If c <> "" Then t = t & c & "< br >"
Next
CConcat = Left(t, Len(t) - 4)
End Function

 

[VSCarter]

Works like a charm! Bravo!