Complications putting text into multiple lines within a cell

  • Thread starter Thread starter DoubleZ
  • Start date Start date
D

DoubleZ

In EXCEL 2007, I have a formula in a cell that says =A1&A2&A3&A4. I am
putting together text from 4 other cells. What I would like to do is put
each one of these texts on individual lines within the cell. I know that
ALT+Enter will accomplish this, but this is a very large worksheet so if I
can do it within the formula that would be ideal.
 
Try this:

=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4

Ensure the cell is set for text wrap, then copy down.

Hope this helps.

Pete
 
This is from Excel 2003, should work the same:

=A1&char(10)&A2&char(10)&A3&char(10)&A4

CHAR(10) will force it to the next row. You should have the column be wide
enough to handle the date, and the row tall enough to handle 4 separate lines.
 
Thanks John C and Pete_UK, but i tried applying your responses and the
outcome is still one line that includes the text of all 4 cells, with a small
box with a question mark in it separating each text.
 
You can enter the formula as others have suggested but for easier typing try
this UDF from Rick, modified to include the linefeed.

Function ConCatRng(CellBlock As Range) As String
'Rick Rothstein, July 5, 2008
Dim X As Long
Dim Z As Long
ReDim Content(1 To CellBlock.Count) As String
For X = 1 To UBound(Content)
If Len(CellBlock(X).Value) > 0 Then
Z = Z + 1
Content(Z) = CellBlock(X).Value
End If
Next
If Z > 0 Then
ReDim Preserve Content(1 To Z)
ConCatRng = Join(Content, Chr(10))
End If
End Function

Usage is: =concatrng(A1:A4)


Gord Dibben MS Excel MVP
 
Back
Top