Hi,
I need some help writing a formula that will concatenate columns by rows but
where the number of columns to be concatenated differs between the rows. I
also need to place comma delimiters between each value.

For Example
Row1: 345 3432 5631 76523 342
Row2: 87 234 546

My results need to show:
Row1: 345,3432,5631,76523,342
Row2: 87,234,546

This sounds easy, but I need Row2 to avoid showing the last two commas:
87,234,546,,

Thanks for the help!

=SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1,REPT(",",5-COUNTA(A1:E1)),"")


"tommykraz" skrev:

> Hi,
> I need some help writing a formula that will concatenate columns by rows but
> where the number of columns to be concatenated differs between the rows. I
> also need to place comma delimiters between each value.
>
> For Example
> Row1: 345 3432 5631 76523 342
> Row2: 87 234 546
>
> My results need to show:
> Row1: 345,3432,5631,76523,342
> Row2: 87,234,546
>
> This sounds easy, but I need Row2 to avoid showing the last two commas:
> 87,234,546,,
>
> Thanks for the help!
>

This UDF will ignore 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(select cells)


Gord Dibben MS Excel MVP

On Wed, 18 Jun 2008 11:20:02 -0700, tommykraz
<(E-Mail Removed)> wrote:

>Hi,
>I need some help writing a formula that will concatenate columns by rows but
>where the number of columns to be concatenated differs between the rows. I
>also need to place comma delimiters between each value.
>
>For Example
>Row1: 345 3432 5631 76523 342
>Row2: 87 234 546
>
>My results need to show:
>Row1: 345,3432,5631,76523,342
>Row2: 87,234,546
>
>This sounds easy, but I need Row2 to avoid showing the last two commas:
>87,234,546,,
>
>Thanks for the help!

Thanks a million...helped a lot.

"excelent" wrote:

> =SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1,REPT(",",5-COUNTA(A1:E1)),"")
>
>
> "tommykraz" skrev:
>
> > Hi,
> > I need some help writing a formula that will concatenate columns by rows but
> > where the number of columns to be concatenated differs between the rows. I
> > also need to place comma delimiters between each value.
> >
> > For Example
> > Row1: 345 3432 5631 76523 342
> > Row2: 87 234 546
> >
> > My results need to show:
> > Row1: 345,3432,5631,76523,342
> > Row2: 87,234,546
> >
> > This sounds easy, but I need Row2 to avoid showing the last two commas:
> > 87,234,546,,
> >
> > Thanks for the help!
> >