Formula to insert alt enter into a cell

[CATraveler]

How can a formula perform the equivalent of the keyboard alt enter in a cell?
ie Make =A1&A2 where A1 and A2 are cells containing text become
a1text
a2text
in the new cell.

 

[James01]

Great reply Peter!

 

[reeset]

If you build your formula with like this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=CONCATENATE(A1,"",A2,"",A3,"",A4)
<o></o>
then physically separate the quotes by using the Alt+Enter method like this in the cell:
<o></o>
=CONCATENATE(A1,"<o></o>
",A2,"<o></o>
",A3,"<o></o>
",A4)
<o></o>
It will also work, weird and great.<o></o>
FYI - If you forget to format the cell to text wrap you'll see a little box with a question in it, and that's how to fix it.

 

[mutthunaveen]

I think there was a typo - I believe it should be CHR(10) and not CHAR(10).

you are excellent..... i was screwing into the first CHAR code.... thanks a lot for correction