Combining Text From Cells

[robin.coe]

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

 

[Gord Dibben]

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

 

[Rick Rothstein]

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)

 

[robin.coe]

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 !!