Carriage returns in the formula bar?

Hi, I have a large table which needs many columns (too many to do manually with alt-enter) joined together in this particular way:

A1: textA
B1: textB

I need to join these two cells with a carriage return like so:

C1: textA
textB

Everything I have tried as a carriage return has only given me the □ character instead of a carriage return. Is there a way to do this?

I will, in advance, admit that this is not an elegant solution, but maybe it
will work for your purposes:

Set cell C1 to "wrap text" (Format . . . Cells . . . Alignment) and then try:
=CONCATENATE(A1,"
",B1)

Where the space between the quotes is filled with spaces. Possible weakness
include: long textA or textB that exceeds the cell width, and printing.
Maybe someone else knows how to get a return into a concatenate function.

"rubeus" wrote:

>
> Hi, I have a large table which needs many columns (too many to do
> manually with alt-enter) joined together in this particular way:
>
> A1: textA
> B1: textB
>
> I need to join these two cells with a carriage return like so:
>
> C1: textA
> textB
>
> Everything I have tried as a carriage return has only given me the
> □ character instead of a carriage return. Is there a way to do
> this?
>
>
> --
> rubeus
> ------------------------------------------------------------------------
> rubeus's Profile: http://www.excelforum.com/member.php...o&userid=36121
> View this thread: http://www.excelforum.com/showthread...hreadid=558967
>
>

Try:
=A1&CHAR(10)&B1

Note: Until you go in and change the format so that text wraps, it will
(most likely) look funny and not appear to have worked.

Format-cells-alignment-wrap text
--
Kevin Vaughn


"rubeus" wrote:

>
> Hi, I have a large table which needs many columns (too many to do
> manually with alt-enter) joined together in this particular way:
>
> A1: textA
> B1: textB
>
> I need to join these two cells with a carriage return like so:
>
> C1: textA
> textB
>
> Everything I have tried as a carriage return has only given me the
> □ character instead of a carriage return. Is there a way to do
> this?
>
>
> --
> rubeus
> ------------------------------------------------------------------------
> rubeus's Profile: http://www.excelforum.com/member.php...o&userid=36121
> View this thread: http://www.excelforum.com/showthread...hreadid=558967
>
>

In C1 enter =A1 & CHAR(10) & B1

Double-click on the fill handle of C1 to copy down.

Format Column C to wrap text.


Gord Dibben MS Excel MVP


On Thu, 6 Jul 2006 10:56:28 -0500, rubeus
<[email protected]> wrote:

>
>Hi, I have a large table which needs many columns (too many to do
>manually with alt-enter) joined together in this particular way:
>
>A1: textA
>B1: textB
>
>I need to join these two cells with a carriage return like so:
>
>C1: textA
>textB
>
>Everything I have tried as a carriage return has only given me the
>□ character instead of a carriage return. Is there a way to do
>this?

"rubeus" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, I have a large table which needs many columns (too many to do
> manually with alt-enter) joined together in this particular way:
>
> A1: textA
> B1: textB
>
> I need to join these two cells with a carriage return like so:
>
> C1: textA
> textB
>
> Everything I have tried as a carriage return has only given me the
> □ character instead of a carriage return. Is there a way to do
> this?

=A1&CHAR(10)&B1
(and remember to format C1 with text wrapping enabled).
--
David Biddulph