How do I insert a hard break (alt enter) in a concatenated string

[Guest]

I'm trying to insert a break at a particular point in text that is combined
using concatenate. The text is being used as column headings and I was hoping
to have some consistency in appearance to make the information easier to
read. The length of the pieces of combined text are of differing lengths. The
column widths are generally the same but do occasionally vary.

Any ideas?

 

[Guest]

Use CHAR(13) and CHAR(10) to insert a new line. For example, this will
concatenate the contents of A1 and B1. B1 will be on the line below A1.

=CONCATENATE(A1,CHAR(13),CHAR(10),B1)

Note that you need to turn on "Wrap text" for the cell. (Format->Cells, in
the Alignment tab, check "Wrap text").

 

[Bob Phillips]

=A1&CHAR(10)&B1

Don't forget that you will also need to set wrap text on the cell and make
the row high enough.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ragdyer]

Would you explain the necessity of Char(13)?

I don't see any need for it at all!

All it gives me is an additional character (square) after the contents of
A1.

 

[Guest]

RD,

I'm not sure if I can explain it very well but I will try. Char(13) is the
ascii carriage return character while Char(10) is the linefeed character. In
an ANSI encoded file, the new line character is two bytes (13 followed by
10). In a UTF-8 or unicode file, the new line character is just one byte and
that is char(10).

You can try it by trying this formula:

=CONCATENATE(A1,CHAR(13),CHAR(10),B1)

Copy the cell and paste in Notepad. Then, try this:

=CONCATENATE(A1,CHAR(10),B1)

Copy the cell and paste in Notepad. I think you'll see the difference.

I don't know why you're getting the square symbol though..

 

[RagDyeR]

Agreed ... in Notepad the results are as requested ... BUT ... in XL02 ...
The Char(13) is superflouous, and in reality, erroneous, as it creates an
unsolicited character.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

RD,

I'm not sure if I can explain it very well but I will try. Char(13) is the
ascii carriage return character while Char(10) is the linefeed character.
In
an ANSI encoded file, the new line character is two bytes (13 followed by
10). In a UTF-8 or unicode file, the new line character is just one byte
and
that is char(10).

You can try it by trying this formula:

=CONCATENATE(A1,CHAR(13),CHAR(10),B1)

Copy the cell and paste in Notepad. Then, try this:

=CONCATENATE(A1,CHAR(10),B1)

Copy the cell and paste in Notepad. I think you'll see the difference.

I don't know why you're getting the square symbol though..