[Solved] Line Break in Calc Concatenate

[dgsarnow]

I am working on OpenOffice 3.0 in a windows vista environment. I have been using oo for about 3 years.

I am looking to concatenate several cells together, but I would like to include a line break so that the data from each cell is on a new line in the cell.

Cell1,Cell2,Cell3
This,is,Cool

Desired output:
This
Is
Cool

I cannot find the syntax for this.

Anyone know how I can do this?

Dave

[squenson]

This is not possible with a formula. The concatenation of strings does not preserve the line breaks, and even the simple formula A2: = A1 does not properly reflect the content of A1 in cell A2 in case A1 contains a line break.

[Trave11er]

Yeah, just tried to do ="Line One" & CHAR(10) & "Line Two"

Calc will always give you:
Line One Line Two

instead of:
Line One
Line Two

Too bad. Anyone know a fix?

Edit: Heh... was just playing around with it. If you Copy -> Paste Special -> Text , it does give you the carriage return .

[exegete77]

The CODE for the line break is 32. However, using CHAR(32) does not reproduce the desired result.

[Trave11er]

Yeah, looks like it reads it as CHAR(32) which is the ASCII space.

[bear454]

=CONCATENATE("Line1",CHAR(10),CHAR(13),"Line2")
=>
Line1
Line2

[squenson]

bear454,

Which version of OOo do you use? With OOo 3.1.0, it gives Line1Line2, and I even had to replace the delimiter "," by ";" to make the formula work...

[acknak]

This is coming soon: in OOo 3.2, due in November. The code is already in the current snapshots, although it's not working exactly right for me (on Linux). See Issue 35913: reference to content with explicit newlines in cells.

If you're interested in this feature, I suggest testing one of the developer snapshots for OOo 3.2 (the "DEV300" code line), available here: http://download.openoffice.org/next/

As always, don't use a snapshot build to edit important files.