Concatenate large numbers of cells

[Seldonian Crisis]

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?

 

[Stephen]

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.

 

[Kevin B]

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.)

 

[Gord Dibben]

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

 

[Seldonian Crisis]

Thank you, I officially owe you a drink.

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.