concatenate question

VSCarter

New Member
Joined
Jun 24, 2008
Messages
31
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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jun 24, 2008
Messages
31
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 24, 2008
Messages
31
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
Joined
Jun 24, 2008
Messages
31

ADVERTISEMENT

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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

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:

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top