Include a hard return in a concatenated string

[CAYMANIAC]

How can include a hard return in a concatenated string? i want to stack multiple cells (that make up an address in this case) in a cell formated to wrap.

try alt+enter

[Barrie Davidson]

On 2002-03-08 09:09, CAYMANIAC wrote:
How can include a hard return in a concatenated string? i want to stack multiple cells (that make up an address in this case) in a cell formated to wrap.

If your cell is formatted to wrap, use this:

="Barrie"&CHAR(10)&"Davidson"

Regards,

[Mark W.]

="the cat"&CHAR(10)&"in the hat"

[CAYMANIAC]

CHAR(10) works great, couldn't get Alt+ Enter to work (I had tried that). Any idea how to avoid wrapping blank cells?

[Mark W.]

{=A1&IF(AND(ISTEXT(A1:B1)),CHAR(10),"")&B1}

{=A1&IF(OR(ISBLANK(A1:B1)),"",CHAR(10))&B1}

Note: These are array formulae which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

Or...

=A1&IF(AND(ISTEXT(A1),ISTEXT(B1)),CHAR(10),"")&B1

=A1&IF(OR(ISBLANK(A1),ISBLANK(B1)),"",CHAR(10))&B1


[ This Message was edited by: Mark W. on 2002-03-08 09:54 ]

[CAYMANIAC]

Thanks