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?

"Seldonian Crisis" wrote in
message ...
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?

I think you will run into several problems doing this. I would use an
iterative approach:
In B1 put the formula =A1
In B2 put the formula =A2&","&B1
Copy this latter formula across row 2 as far as you need.

Skip the concatenate formula and just use the cell addresses as follows:

=A1&", "&B2&", "&C3 (adding &", "& after all cell addresses with the
exception of the last one.)
--
Kevin Backmann


"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?

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?

Thank you, I officially owe you a drink.

"Stephen" wrote:

"Seldonian Crisis" wrote in
message ...
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?

I think you will run into several problems doing this. I would use an
iterative approach:
In B1 put the formula =A1
In B2 put the formula =A2&","&B1
Copy this latter formula across row 2 as far as you need.