Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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.

share|improve this question
    
I've added two screenshots to illustrate the issue... –  tohuwawohu Jun 5 '12 at 13:13

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
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 '12 at 12:23
    
ah - ok, i understand. i've emended my answer to cover this. –  tohuwawohu Jun 5 '12 at 13:13
    
@tohuwawohu nice. can this be adapted so that separate text stays seperate? (<text><empty line><text> becomes <texttext>.) –  Detnuomnu Jun 5 '12 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 '12 at 14:11

Download LO Calc guide here and go to page 32!

share|improve this answer
    
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 '12 at 11:04

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. *

share|improve this answer
    
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 '12 at 13:16
    
Someone must have changed the meaning of all. –  Detnuomnu Jun 5 '12 at 14:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.