7

I have this simple formula:

=CONCATENATE("foo";"bar")

which renders foobar on the cell.

How can I lay this formula in multiple lines in the same cell? I'd like to be able to do something like the following but it doesn't work as the newline in the cell forbids the formula from being executed:

=CONCATENATE("foo";
             "bar")

The reason I'm asking is because I have huge formulas an I need to format them (using newlines and a bit of indentation) for readability.

thanks

3 Answers 3

1

To insert a line break in the formula's result, this should work:

=CONCATENATE("foo";CHAR(10);"bar")

A structured overview over the current formula is provided by the function wizard (Ctrl+F2); it should directly display the Structure tab if the current cell already holds a formula.

2
  • Thanks but I don't want to output a newline as a result of the expression. I want to split my long expression with newline(s) for enhancing readability.
    – cherouvim
    Jun 28, 2011 at 8:17
  • @cherouvim: oh, sorry, i see. Then, there's only the function wizard i can offer. I've amended my previous answer accordingly.
    – tohuwawohu
    Jun 28, 2011 at 10:36
1

I tried to do this with LibreOffice Calc, v3.5.4.2 (!), and while I was able to enter (via CTRL-ENTER) multiple carriage-returns while editing the formula, once I pressed enter, it collapsed them into spaces.

You COULD edit your formulae in another editor, using the line breaks as desired, then copy & paste this into a cell. But that's still not optimal.

alas, there seems no hope. At least not today.

1
  • But after pasting in a cell and pressing enter Calc will remove all newlines. Aug 14, 2016 at 16:37
1

This behavior in LibreOffice / OpenOffice makes me sick too. The only solution I've found - that makes sense without destroying the sheet or saving formatted functions in external files - is to paste the formatted (with newlines, indents) function as the header cell comment (right click on the header cell -> Add comment -> paste the formatted function). That way it won't be visible, and you don't have to store it somewhere else and you'll be able to access the formatted function by editing the comment (right click on the cell -> Edit comment -> now copy the content).

Hope this helps and I hope this problem will be solved by OpenOffice / LibreOffice developers ;-)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .