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 :P

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

Make sure the cell is formatted to wrap text.

"Bretter99" wrote:

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 :P

Hi

Try
=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4
Set the format of the cellalignmentWrap text

--
Regards

Roger Govier


"Bretter99" wrote in message
...
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 :P

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?

"Roger Govier" wrote:

Hi

Try
=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4
Set the format of the cellalignmentWrap text

--
Regards

Roger Govier


"Bretter99" wrote in message
...
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 :P

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



"JLatham" wrote:

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

Make sure the cell is formatted to wrap text.

"Bretter99" wrote:

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 :P

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

On Fri, 5 Jun 2009 19:26:02 -0700, AfricanTigress
wrote:

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(1 0),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



"JLatham" wrote:

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

Make sure the cell is formatted to wrap text.

"Bretter99" wrote:

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 :P

"Bretter99" wrote:

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 :P

"Bretter99" wrote:

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 :P

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

"Bretter99" wrote:

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 :P

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

Make sure cell is set to wrap text.


Gord Dibben MS Excel MVP

On Mon, 15 Mar 2010 09:53:01 -0700, leanjinja
wrote:



"Bretter99" wrote:

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 :P