You really want them stacked as per your B1 example?

Set B1 to wraptext and row 1 to autofit.

Function ConRange(CellBlock As Range) As String
For Each Cell In CellBlock
ConRange = ConRange & Cell.Value & Chr(10)
Next
End Function

=ConRange(A1:A100) entered in B1

Chr(10) is a linefeed....................remove the & Chr(10) if you
don't need it.


Gord

On Fri, 13 Jan 2012 08:49:06 -0800 (PST),
wrote:

If I have 100 cells that contain text and I want to write an Excel
formula that will combine all cell texts into cell without using
=a1&a2&a3 what would I do? The volume of cells is too large to use
the & in the formula and the transpose feature does not combine text
into one cell.

A1 = one
A2 = two
A3 = three
A4 = four
A5 = five


I want B1 to = one
two
three
four
five