You could use a UDF.

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(range)


Gord Dibben MS Excel MVP

On Tue, 27 Nov 2007 06:09:00 -0800, Seldonian Crisis
wrote:

I've got to concatenate 416 cells within a row, the resulting text also needs
to be split by commas . do i have to manually write out
CONCATENATE(A1,CHAR(44),B1,CHAR(44),C1... etc or is there a way of speeding
this up?