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

On Jul 11, 7:04*pm, DoubleZ wrote:
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.

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.


--
John C


"DoubleZ" wrote:

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.

make that, column wide enough to handle the data!
--
John C


"John C" wrote:

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.


--
John C


"DoubleZ" wrote:

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.

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.

"Pete_UK" wrote:

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

On Jul 11, 7:04 pm, DoubleZ wrote:
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.

Sorry, I forgot to check text wrap. Thank you for your help.

"Pete_UK" wrote:

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

On Jul 11, 7:04 pm, DoubleZ wrote:
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.

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

On Fri, 11 Jul 2008 11:04:00 -0700, DoubleZ
wrote:

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.

You're welcome.

Pete

On Jul 11, 9:33*pm, DoubleZ wrote:
Sorry, I forgot to check text wrap. *Thank you for your help.