concatenate more than 30 cells?

Answer 1

If you can't get it done using a formula... use a macro instead. If your values are in a col (say Col A), from row x to x+29 do this:

Sub ConcatAll()
For rowCount = x to x+29
concatenated = concatenated & range("A" & rowCount)
Next rowCount
Range("B1") = concatenated
End Sub

if your values are in a single row (say row 1) spanned across cols starting from Col A, then do this:

Sub ConcatAll()
startCol=65
For colCount = startCol to startCol + 29
concatenated = concatenated & Range(Chr(startCol) & 1)
Next colCount
Range("B1") = concatenated
End Sub

Either of the above will concatenate and populate cell B1 with the value.

.

Answer 2

Although less elegant than coding a macro you can also perform the concatenation in stages.

Concatenate 5 groups of 6 cells. Then perform a final concatenation of these results. You may hide the intermediate concatenation cells displaying only the final group concatenation..