How can i add a line break into a formula in excel 2000?

[Guest]

For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
put the result into a single excel cell like this:
I
LIKE
BEEF
BURGERS

But i cant see how to manually insert a line break into my statement as in
something like:
=concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
Any suggestions would be appreciated, thanks

 

[Guest]

=CONCATENATE(A1,CHAR(10),A2,CHAR(10),A3,CHAR(10),A4)
or
=A1 & CHAR(10) & A2 & CHAR(10) & A3 & CHAR(10) & A4

Make sure the cell is formatted to wrap text.

 

[Roger Govier]

Hi

Try
=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4
Set the format of the cell>alignment>Wrap text

 

[Margaret Legge]

I am creating a file with sqr. A chr(10) --- which is supposed to be a line
feed --- not only does a line feed but also a carriage return. How do I set
the format of a cell in an sqr?

 

[AfricanTigress]

Hi there - thanx for this ... it reallly helped me. Since your formula
assisted me once, perhaps you could help me with this.

This formula works but I would like to tell the formula to omit lines or
that are blank.

i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)

Say line K15, L15 and M15 had no data like this :

"227 Finney Lane
Heald Green



Cheadle
SK8 3QB"

How can I tell it to omit the blank lines?

Your help would be much appreciated.

Thanx

 

[Gord Dibben]

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

In a cell enter =ConCatRange(I15:O15)

The blank cells will be ignored.

Format the cell to "Wrap Text"


Gord Dibben MS Excel MVP

 

[leanjinja]

For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
put the result into a single excel cell like this:
I
LIKE
BEEF
BURGERS

But i cant see how to manually insert a line break into my statement as in
something like:
=concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
Any suggestions would be appreciated, thanks

 

[leanjinja]

For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
put the result into a single excel cell like this:
I
LIKE
BEEF
BURGERS

But i cant see how to manually insert a line break into my statement as in
something like:
=concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
Any suggestions would be appreciated, thanks

You could always use alt+enter - it just starts a new line within a cell.

 

[leanjinja]

For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
put the result into a single excel cell like this:
I
LIKE
BEEF
BURGERS

But i cant see how to manually insert a line break into my statement as in
something like:
=concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
Any suggestions would be appreciated, thanks

 

[Gord Dibben]

=A1 & CHAR(10) & A2 & CHAR(10) & on and on

Make sure cell is set to wrap text.


Gord Dibben MS Excel MVP