Combining Text From Cells
 

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

Combining Text From Cells
 

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

Combining Text From Cells
 

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)

Combining Text From Cells
 

On Jan 14, 3:14*pm, "Rick Rothstein"
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 !!