concatenate formula for rows with differing amount of columns
 

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!

concatenate formula for rows with differing amount of columns
 

=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!

concatenate formula for rows with differing amount of columns
 

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
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!

concatenate formula for rows with differing amount of columns
 

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!