Concatenate cell range, separate with comma except for empty cells

Hi,

It's not very elegant but this will do what you want.

=SUBSTITUTE(TRIM(C40&" "&C41&" "&C42&" "&C43&" "&C44&" "&C45&" "&C46&" "&C47&" "&C48&" "&C49&" "&C50&" "&C51&" "&C52&" "&C53&" "&C54&" "&C55&" "&C56&" "&C57)," ",",")

You could use this custom function:

Function Concat(rng As Range, Optional sep As String = ",") As String
    Dim rngCell As Range
    Dim strResult As String
    For Each rngCell In rng
        If rngCell.Value <> "" Then
            strResult = strResult & sep & rngCell.Value
        End If
    Next rngCell
    If strResult <> "" Then
        strResult = Mid(strResult, Len(sep) + 1)
    End If
    Concat = strResult
End Function

Use like this:

=Concat(C40:C57)

If you store the function in a module in your personal macro workbook Personal.xlsb, so that it is available in all workbooks, use

=Personal.xlsb!Concat(C40:C57)

It is to my understanding that MoreFunc does not work with excel 2013 (or 2010 for that matter). And so I am looking to a formula similar to MCONCAT that will take the cell range C40:C57 and put the text that is is in each cell ad concantinate it, but seperating them with commas. I currently am using =CONCATENATE(C40:C57) but that yields '1Tuna tin(s)2Brown rice1Mixed Veg2Greek yogurt2Almonds1Porridge + milk1whey protein', and does not separate them with commas.

 

You might find the UDF I posted in my mini-blog article here way more than flexible enough for your needs...

Flexible Concatenation Function

and/or possibly this related article...

FORMATTED Flexible Concatenation Function

Hi,

The Morefunc addin works very well with Excel 2010 and 2013.  I have used it in both versions.  The process for installing the Morefun addin in Excel 2010/2013 has been described at the following link on my website (Point 4) - http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

Hope this helps.

Hi,

 

The Morefunc addin works very well with Excel 2010 and 2013.  I have used it in both versions.  The process for installing the Morefun addin in Excel 2010/2013 has been described at the following link on my website (Point 4) - http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

 

Hope this helps.

It is to my understanding that MoreFunc does not work with excel 2013 (or 2010 for that matter). And so I am looking to a formula similar to MCONCAT that will take the cell range C40:C57 and put the text that is is in each cell ad concantinate it, but seperating them with commas. I currently am using =CONCATENATE(C40:C57) but that yields '1Tuna tin(s)2Brown rice1Mixed Veg2Greek yogurt2Almonds1Porridge + milk1whey protein', and does not separate them with commas.

 

You might find the UDF I posted in my mini-blog article here way more than flexible enough for your needs...

 

Flexible Concatenation Function

 

and/or possibly this related article...

 

FORMATTED Flexible Concatenation Function

 

Hi,

Do you have the three files (Morefunc, Morefunc11 and Morefunc12) at the following location?

C:\Program Files (x86)\Microsoft Office\Office15\Library

Also, in the Excel Add-ins window, the following boxes should be checked

Morefunc (add-in functions)

Morefunc Tools

Morefunc12

Lastly, I have tried this on the following configuration

1. Windows 32 bit (Windows 7 as well as Windows 8)

2. Excel 32 bit (Excel 2007, Excel 2010, Excel 2013)

If you have Excel 64 bit, then this will not work.

You might find the UDF I posted in my mini-blog article here way more than flexible enough for your needs...

 Flexible Concatenation Function

 

and/or possibly this related article...

 

FORMATTED Flexible Concatenation Function

 Your UDF code worked superb. Only problem is that I said to leave blank spaces, but what I meant to say was ingore cells with 0 in. The current result is '

0,0,1Tuna tin(s),0,2Brown rice,0,0,1Mixed Veg,0,2Greek yogurt,2Almonds,0,0,0,0,0,1Porridge + milk,1whey protein'

Let's say your formula to produce that output was this...

=ConCat(...)

and that none of the cells that you are concatenating has any values ending in 0... under this assumption, you can change the above formula to this....

=SUBSTITUTE(ConCat(...),"0,","")

and it will eliminate the zeroes.

Hi,

 

Do you have the three files (Morefunc, Morefunc11 and Morefunc12) at the following location?

 

C:\Program Files (x86)\Microsoft Office\Office15\Library

 

Also, in the Excel Add-ins window, the following boxes should be checked

 

Morefunc (add-in functions)

Morefunc Tools

Morefunc12

 

Lastly, I have tried this on the following configuration

 

1. Windows 32 bit (Windows 7 as well as Windows 8)

2. Excel 32 bit (Excel 2007, Excel 2010, Excel 2013)

 

If you have Excel 64 bit, then this will not work.

 

You might find the UDF I posted in my mini-blog article here way more than flexible enough for your needs...

 Flexible Concatenation Function

 

and/or possibly this related article...

 

FORMATTED Flexible Concatenation Function

 Your UDF code worked superb. Only problem is that I said to leave blank spaces, but what I meant to say was ingore cells with 0 in. The current result is '

0,0,1Tuna tin(s),0,2Brown rice,0,0,1Mixed Veg,0,2Greek yogurt,2Almonds,0,0,0,0,0,1Porridge + milk,1whey protein'

Let's say your formula to produce that output was this...

 

=ConCat(...)

 

and that none of the cells that you are concatenating has any values ending in 0... under this assumption, you can change the above formula to this....

 

=SUBSTITUTE(ConCat(...),"0,","")

and it will eliminate the zeroes.