8

In Libre office calc I have a bunch of cells that contain empty lines. A single cell looks like this:

breaks

How can I delete the empty lines from the cells to get cells that look like this:

no breaks

I do not want to edit 200 cells by hand to delete the blank lines. I'm sure there is a faster way.

P.S. I obtained these cells by copying a table from a website into Libre office calc. For that I used the clip-board.

1
  • I've added two screenshots to illustrate the issue...
    – tohuwawohu
    Jun 5, 2012 at 13:13

3 Answers 3

8

I assume you want to remove multiple empty lines in one step if possible. To remove a single line, there's a context menu entry.

There are two possible approaches to remove multiple lines without much hassle:

  • as proposed in this answer: sort the lines alphabetically so the empty lines get sorted to the start or end, then delete them.

  • as proposed in this blog post: use filtering to get only the blank lines displayed, then select and delete them, then remove the filter.

EDIT:

To remove hard line breaks inside cells automatically, you could use the search / replace functionality and regular expressions. Just search for \n and let the replace box empty. Don't forget to activate "Regular expressions" under More Options.... You won't see a result immediately, since Calc doesn't change the row height automatically. So you will have to set the row height using Optimal row height... in the context menu after removing the line breaks.

search - replace

EDIT 2:

It may be possible that there aren't any empty lines in the cells, so the search/replace operation may fail. This is because LibreOffice automatically applies styles from the original html tables when pasting data from the web. To make sure that it isn't just a row format issue, just select the complete sheet and select Optimal height... from Menu Format -> Row.

4
  • I'm sorry, my question was not very clear. I want to delete empty lines within a cell. I adapted the question.
    – sjdh
    Jun 5, 2012 at 12:23
  • ah - ok, i understand. i've emended my answer to cover this.
    – tohuwawohu
    Jun 5, 2012 at 13:13
  • @tohuwawohu nice. can this be adapted so that separate text stays seperate? (<text><empty line><text> becomes <texttext>.)
    – Detnuomnu
    Jun 5, 2012 at 13:56
  • hmm - this is a little bit difficult, since this depends on the content details of your table. The easiest way would be to replace the \n by a single space. But this may result in multiple subsequent spaces or in leading spaces. So, additional search/replace runs using regular expressions may be required.
    – tohuwawohu
    Jun 5, 2012 at 14:11
0

from Libreoffice help:

*CLEAN

All non-printing characters are removed from the string. Syntax

CLEAN("Text")

Text refers to the text from which to remove all non-printable characters. *

2
  • Good find, but doesn't remove all the line breaks for me. Did you test it? It leaves one hard line break when trying to remove multiple subsequent line breaks.
    – tohuwawohu
    Jun 5, 2012 at 13:16
  • 1
    Someone must have changed the meaning of all.
    – Detnuomnu
    Jun 5, 2012 at 14:13
-2

Download LO Calc guide here and go to page 31!

1
  • 2
    Did you mean Page 31? Maybe you should add the relevant information to your answer, since if the link gets invalid, your answer gets useless, too.
    – tohuwawohu
    Jun 5, 2012 at 11:04

You must log in to answer this question.

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