# concatenate question

#### VSCarter

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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Jonmo1

##### MrExcel MVP
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

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

")

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

##### MrExcel MVP
This is very easy with a UDF:

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

##### MrExcel MVP

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

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

Last edited:

#### VSCarter

##### New Member

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

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

etc.
Remove the extra spaces in the br tags in either the formula or code.

Revised UDF:

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

Last edited:

#### VSCarter

##### New Member
Works like a charm! Bravo!

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,013
Messages
5,856,828
Members
431,835
Latest member
AJBud2022

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