concatenate formula for rows with differing amount of columns

[tommykraz]

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!

 

[excelent]

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


"tommykraz" skrev:

 

[Gord Dibben]

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

 

[tommykraz]

Thanks a million...helped a lot.

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


"tommykraz" skrev: