I would like to combine the words from different cells into one cell - sometimes 1 or 2 cells are left empty Example A1 A2 A3 A4 A5 Peter Simon Derek Empty Kent combine into one cell marking all =A1&","&A2&","&A3&","&A4&","&A5 Should say Peter, Simon, Derek, Kent (without extra , or space for empty cell included) Could somebody help me with formula for this? would be greatly appreciated

This UDF ignores blank cells. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ", " Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =concatrange(A1:A5) Gord Dibben MS Excel MVP On Sat, 25 Jul 2009 10:11:01 -0700, mh <> wrote: >I would like to combine the words from different cells into one cell - >sometimes 1 or 2 cells are left empty > >Example >A1 A2 A3 A4 A5 >Peter Simon Derek Empty Kent > >combine into one cell marking all >=A1&","&A2&","&A3&","&A4&","&A5 > >Should say >Peter, Simon, Derek, Kent (without extra , or space for empty cell included) > >Could somebody help me with formula for this? would be greatly appreciated

Hi, Here is a custom function that seems to do what you want: Function myConCat(S As Range) As String Dim cell As Range Dim con As String Dim I As Integer I = 1 For Each cell In S If I = 1 Then con = cell ElseIf cell <> "" Then con = con & ", " & cell End If I = I + 1 Next cell myConCat = con End Function so you would enter =myConCat(A1:A10) or use any other range. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "mh" wrote: > I would like to combine the words from different cells into one cell - > sometimes 1 or 2 cells are left empty > > Example > A1 A2 A3 A4 A5 > Peter Simon Derek Empty Kent > > combine into one cell marking all > =A1&","&A2&","&A3&","&A4&","&A5 > > Should say > Peter, Simon, Derek, Kent (without extra , or space for empty cell included) > > Could somebody help me with formula for this? would be greatly appreciated >

Also, if I understand your question, you need to make a slight modification of the other suggestion: =SUBSTITUTE(TRIM(A1&" "&A2&" "&A3&" "&A4&" "&A5)," ",", ") note the extra space after the final comma. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "mh" wrote: > I would like to combine the words from different cells into one cell - > sometimes 1 or 2 cells are left empty > > Example > A1 A2 A3 A4 A5 > Peter Simon Derek Empty Kent > > combine into one cell marking all > =A1&","&A2&","&A3&","&A4&","&A5 > > Should say > Peter, Simon, Derek, Kent (without extra , or space for empty cell included) > > Could somebody help me with formula for this? would be greatly appreciated >

Hi, You may download and install this addin - http://www.download.com/Morefunc/3000-2077_4-10423159.html Now use the mconcat(range) function -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "mh" <> wrote in message news:... > I would like to combine the words from different cells into one cell - > sometimes 1 or 2 cells are left empty > > Example > A1 A2 A3 A4 A5 > Peter Simon Derek Empty Kent > > combine into one cell marking all > =A1&","&A2&","&A3&","&A4&","&A5 > > Should say > Peter, Simon, Derek, Kent (without extra , or space for empty cell > included) > > Could somebody help me with formula for this? would be greatly appreciated >

I know this post is old, but I'd like to thank you guys for the great formulas. I have one mod I'm hoping someone can help me out with. Right now I'm using the following in VBE: Function myConCat(S As Range) As String Dim cell As Range Dim con As String Dim I As Integer I = 1 For Each cell In S If I = 1 Then con = cell ElseIf cell <> "" Then con = con & ", " & cell End If I = I + 1 Next cell myConCat = con End Function I'd like to be able to combine from 2 or 3 ranges into the same cell so I can exclude a few cells that are acting as headers withing the range. Any way to do this? For instance I want cells C5:C11, C20:25 to all show up in the same cell and be separated by commas.