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

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), (E-Mail Removed)
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

How about this one-liner macro...

Sub JoinColumnA()
Range("B1").Value = Join(WorksheetFunction.Transpose(Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)), vbLf)
End Sub

Rick Rothstein (MVP - Excel)

On Jan 14, 3:14*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> How about this one-liner macro...
>
> Sub JoinColumnA()
> * Range("B1").Value = Join(WorksheetFunction.Transpose(Range("A1:A" &_
> * * * * * * * * * * * Cells(Rows.Count, "A").End(xlUp).Row)), vbLf)
> End Sub
>
> Rick Rothstein (MVP - Excel)

This works great....thank you !!