Ask Your Question

(Calc) How do you merge cells and preserve the structure?

asked 2016-09-29 06:59:58 +0200

Double gravatar image

updated 2016-09-29 07:01:17 +0200

I'm trying to merge a row of cells, and I want each bird to stay in its original position (basically, without the lines between them). How would I achieve this?

When I highlight the 5 cells and hit 'Merge Cells', it throws all birds on the same line... looks awful:

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-09-29 12:28:37 +0200

Lupp gravatar image

updated 2016-09-29 22:13:13 +0200

The structure clearly is that you have multiple species accessible in a simple way. Merging of the cells will inevitably destroy this structure. The ultimate goal you might achieve transferring the multiple items into one cell is a kind of preservation of the view (in multiple lines).

You should consider again the reasons for merging. In most cases they are not valid. With respect to the typical functionality of spreadsheets merging of cells is mostly evil. If you just are arranging some entries for a view to print it later, there is no reason for merging since the cell grid is not printed by default. (Expressly not recommended: You can also suppress it for the screen view.) Creating a printable border for the cell range would definitely be the better solution as compared with merging.

As far as I know there is no way to change the delimiter inserted on merging with 'Yes' answered to the prompt which is a space (ASCII 32). This would be needed for getting the result you want to achieve since the linefeed inside a cell is made by inserting an LF character (ASCII 10).

There is, however a recently implemented function TEXTJOIN which should be capable to get your desired result in another cell based on a formula without merging and using 'Optimal Height' instead. Unfortunately this new function has still some flaws.

The attached example is demonstrating a solution based on the new TEXTJOIN made with a trick and an alternative solution by the user function "MyTextJoin" coded by me some time ago (in BASIC) for the same purpose and not suffering from the same flaws.

My answer to the original question is still "Not at all!". The abstraction stressed above that it is, strictly spoken, impossible, aside, we may focus on something coming near to the assumed intentions. @mark_t already gave a hint, I will attach this new example also containing some explanations.
Please also note: 'Calc' originates from "calculations" spreadsheets were originally made for. If you are decisive to not calculate at all with your data (not even a concatenation of texts), you may consider to use a table in a 'Writer' document for your purposes. See also this example.
Also in Calc you may enter your list from the beginning in to one cell hitting Ctrl+Enter to insert line breaks.

edit flag offensive delete link more


Hmm.. sounds more complicated than it should be. I am only using LibreOffice for personal note purposes. I have no desire to print or use these notes for business or mathematics. Merging the cells is important to me because it keeps everything neater and more organized. So far, I have only been able to make cells appear the way I want by copy/pasting the cells from another program like Evernote using the Paste Special/HTML function. I'm definitely not familiar with macros... ugh!

Double gravatar imageDouble ( 2016-09-29 20:02:54 +0200 )edit

answered 2016-09-29 21:29:38 +0200

mark_t gravatar image

If you select the cell range and then set the background to white then the cell grid lines will not show for that range of cells. This avoids turning off the cell grid lines in the screen view.

Example in Untitled 28.ods

edit flag offensive delete link more


This depends on the setting for 'Visual Aids' > 'Grid lines'. There is an option 'Show grid lines on couloured cells' which will thwart the success of @mark_t's advice.

Lupp gravatar imageLupp ( 2016-09-29 21:51:42 +0200 )edit

Thanks @Lupp, I didn't know there was option to show grid lines on coloured cells. Maybe it's turned off by default so I never noticed.

mark_t gravatar imagemark_t ( 2016-09-29 22:13:01 +0200 )edit

@mark_t: I am rather sure you are rtight. The default should be simply 'Show'.

Lupp gravatar imageLupp ( 2016-09-29 22:16:02 +0200 )edit

@Lupp: default is set to match Excel which does not seem to have an option to show the gridlines on coloured cells.

mark_t gravatar imagemark_t ( 2016-09-29 22:28:58 +0200 )edit

@mark_t: Another strong reason then not to use Excel. Thanks.

Lupp gravatar imageLupp ( 2016-09-29 22:37:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-29 06:59:58 +0200

Seen: 560 times

Last updated: Sep 29 '16