7

For example:

Cell A1 = 2/3/2012
       A2 is empty
       A3 is empty
       A4 = 3/7/2012
       A5 is empty
       A6 is empty
       A7 is empty

What I want to do is:

  • select the complete list (A1 to A245),
  • take some action (e.g., click a button) that will automatically select only blank cells,
  • enter a formula (or a constant),
  • press Alt+Enter

and have the blank cells filled with that value.

But this doesn't happen.

  1. I can't select all the non-empty cells.
  2. Even if I select the non-empty cells manually, I can't fill them with Alt+Enter; the error message is displayed as "Insert into multiple selection not possible" (LibreOffice 3.6).

I have to use this feature for audit. (It can be done in Microsoft Excel by the "Go To" function, then select 'blanks').

4

Upgrade to LibreOffice 4:

Then you'll find under the Edit menu, a Fill sub-menu, and under that, an item called Down; this gives the contents of the first cell selected to all the other cells.

| improve this answer | |
  • This answered my question! I googled 'libreoffice calc fill column with formula' and got straight here. Just what I wanted and saves dragging fill handles hundreds of rows down – John Lawrence Aspden Oct 20 '16 at 18:51
  • 4
    In LibreOffice 5 this is under the "Sheet" menu. However, this also fills non-blank cells in addition to blank cells, which is not the desired behavior for the example above or any case where there are some values in the specified range. – Max Candocia Oct 27 '17 at 20:25
3

What you can do is the following:

  • Select an empty adjacent column, let's say B (insert a new column if you have data in B already)
  • In the B1 enter =IF(A1,A1,<your formula or constant>)
  • With B1 still selected press Ctrl+Shift+End then Ctrl+D to copy this formula for the rest of the column

The column B should have the data you were looking for

| improve this answer | |
3

In LibreOffice 4.0 you can do a search and replace:

  1. select the area in which you want to fill empty cells
  2. Edit > Search & Replace
  3. search for nothing but check “[x] Entire cells“ and if you have done step 1 (selection) check also in “More options” “[x] current selection only”
  4. insert into the field of replace the text you want to replace

This may also work in previous versions of LibreOffice.

| improve this answer | |
  • It sort of worked, but there are 2 problems: major: It replaces the contents with string formatting (e.g., '0 instead of 0) when you replace it, and I can't change that. minor: It does not change the edit history at all, and it cannot be undone unless you reload a previous version of the file. – Max Candocia Oct 27 '17 at 20:22
2

This is a lot more difficult in LO than in Excel! Even using Find Replace with Regular Expressions with ^$ doesn't work.... There is a solution, but it's not elegant.

Select the whole area (cells with content and blank cells, ex : A1:A50)
Ctrl+C
Select the area from 1st blank cell till the end, ex : A3:A50
Press F2, then write =A2 (or A49) 'cell above cell with focus
Alt+Enter
Select again the whole area
Ctrl+Shift+V
Check 'text', 'number', may be 'format' (what you want to copy) and 'skip empty cells'.

Found this at: OpenOffive Forum

| improve this answer | |
1
  1. Select an empty adjacent column, let's say B. (insert a new column if you have data in B already)
  2. In the B1 enter: =IF($A2="";$B1;$A2)
  3. With B1 still selected press Ctrl+Shift+End then Ctrl+D to copy this formula for the rest of the column.
| improve this answer | |
1

i'm using libreoffice 6.4.4.2 . To quickly fill down multi hop blank cells do the following steps:

  1. select the area in which you have your data (including blank cells where you want to fill value from nearest above non-blank cell)
  2. do ctrl+H (it will pop up find and replace window )
  3. put ^$ in 'find:' text box (to blank cells and select them when clicking 'Find All'/'Replace All' button)
  4. put @=INDIRECT(CONCATENATE("R",(ROW()-1),"C",COLUMN()),0) in 'Replace:' text box ( to replace blank cells with this formula - I added @ because formula was being treated as text if I did not put @ and later replaced it with blank)
  5. Click on 'Replace All' button (it will put @=INDIRECT(CONCATENATE("R", (ROW()-1),"C",COLUMN()),0) in all blank cells)
  6. Put @ in 'find:' text box (we want @ to be replaced)
  7. leave blank 'replace:' text box (we want @ removed)
  8. Click on 'Replace All' button (it will replace cells with @=INDIRECT(CONCATENATE("R", (ROW()-1),"C",COLUMN()),0) with =INDIRECT(CONCATENATE("R",(ROW()-1),"C",COLUMN()),0) )
  9. do ctrl+A (or manually select area which include the cell which were hitherto blank)
  10. copy this area
  11. paste as unformulated text

As the @ symbol is removed from the cells INDIRECT formula gives output same as value of nearest non-blank above cell

| improve this answer | |
0

recently I have to faces the same problem.

Thanks to Alex P I solved my problem.

in cell B1 enter

=IF(A1="A1","2/3/2012", IF(A1="A2","empty", IF(A1="A3","empty",IF(A1="A4", "3/7/2012", "do not match"))))

the recursion will be very very deep cause your have 200+ values in cell 'A1`

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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