How to select only full cells in a column - Libreoffice / Openoffice Calc

Imagine having a column in a spread sheet with many lines in which not every line is full; but we want to select only the ones that are full. I'm doing this with a "Find" dialogue box and when wondering if there exists a simpler solution I only came across more complicated ones :/.

Here's is how I do it:

  1. Select the data set in the row with empty and not empty cells.
  2. Open Find & Replace dialogue box (Edit -> Find & Replace from the menu).
  3. In the "Search for" field write this regular expression: .*
  4. Click on "More options" and check "Regular expressions" and "Current selection only".
  5. Click on "Find All"
  6. Copy and Paste wherever wanted.


Hope this is clear enough.

Needless to say that with regular expressions it is possible to select various things:
- Cells containing letters only: [A-z]
- Cells containing numbers only: [0-9]

- ...

For various examples of regular expression see these examples. It's worth learning them.

Trackbacks

Trackback specific URI for this entry

This link is not meant to be clicked. It contains the trackback URI for this entry. You can use this URI to send ping- & trackbacks from your own blog to this entry. To copy the link, right click and select "Copy Shortcut" in Internet Explorer or "Copy Link Location" in Mozilla.

No Trackbacks

Comments

Display comments as Linear | Threaded

Giavi on :

Hello, that's great!! Unfortunately I need exactly the opposite :-D
I need to select only blank cells! I'm sure that a very similar operation has to be done in order to achieve that. Perhaps I have to type something else instead of ".*"??
Thank you,

Bye

mkljun on :

The regex for empty string is "^$". However, this doesn't work in Libre/OpenOffice. The rationale behind is explained here:

"Searching for the regular expression '^$' will not find empty cells. This is intentional - the rationale being to avoid performance issues when selecting a huge number of cells. Note that empty cells will not be found even if you are only searching a selection."

From https://wiki.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Calc

However, there are other options. Depends really on what you try to achieve. This might be of help:

http://ooo-forums.apache.org/en/forum/viewtopic.php?f=9&t=948

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Can you please write (or copy/paste) this text in the field below: i h a t e s p a m