Concatenate cell range, separate with comma except for empty cells

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.

Thanks in advance.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

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)," ",",")

If this response answers your question then please mark as answer.

Mike H

10 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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)

---
Kind regards, HansV
https://www.eileenslounge.com

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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


 

*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

I installed it but got a compile error. I followed the steps exactly. The only thing was I didn't have an Office13 folder, instead I had Office12 and Office 15. I copied the files to the Office15 folder. When adding the add-ons I had to browse to find them, they didn't appear in the list.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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


 

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'

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

 

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

 

 

*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

 

Oh I have 64-bit. Thank you for the help anyway.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

 

 

I changed the formula to =SUBSTITUTE(concat(C39:C55),"0,","") but it says #VALUE!

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated June 17, 2020 Views 42,744 Applies to: