We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How can one "search and replace" (in calc) an empty cell with the number 0?

asked 2021-06-05 11:12:19 +0200

Patlab gravatar image

updated 2021-06-05 11:57:40 +0200

It gives always a textual 0 ('0) as replacement!

edit retag flag offensive close merge delete


thereis no way to find empty cells in calc -search&replace!

It gives always a textual 0 ('0) as replacement!

What exactly is It ?

karolus gravatar imagekarolus ( 2021-06-05 13:04:07 +0200 )edit

It=the replacement. "Find" empty cells (=nothing) "& replace" it with 0. You get textual 0's, not numerical ones, as clicking on a cell with such 0 let you see a '0 in the formula-bar. Replacing such '0 with a 0 changes the '0 to a numerical 0 (but you get funny results in other cell's without '0!). I have no mumeric keypad on my notebook, perhaps then you get a numeric 0?

Patlab gravatar imagePatlab ( 2021-06-05 13:41:48 +0200 )edit

5 Answers

Sort by » oldest newest most voted

answered 2021-06-06 09:43:39 +0200

Patlab gravatar image

updated 2021-06-06 16:53:33 +0200

Combining Karolus, LeroyG and erAck I found the following easy solution with two times search and replace: 1) search empty (=nothing) and replace with 0 After that only the cells with a replaced '0 are still selected (even in an irregular range) and now "abracadabra"... 2) with the same search and replace-box, but search 0 (instead of empty) and replace with the same 0(with options: [x]Regular expression and [x]Selection only) ... DONE...Works also for an other number... Thanks very very much (sorry, I'm not allowed to give you upvotes).

edit flag offensive delete link more

answered 2021-06-06 11:56:23 +0200

Kamil Landa gravatar image

Select some cells (you can use multiselection) and then the macro zeroToEmptyCells will put zeroes to the empty cells in the selection

Sub zeroToEmptyCells 'put 0 to the empty cells in the selection
    dim oDoc as object, oSel as object, oCells as object, i&, o as object
    if oSel.supportsService("com.sun.star.sheet.SheetCellRanges") then 'for multiselection
        for i=0 to oSel.Count-1 'seek all ranges
            o=oSel.getByIndex(i) 'one range from selection
        next i
    elseif oSel.supportsService("com.sun.star.sheet.SheetCellRange") then 'one range in selection
    end if
End Sub

Sub setZero(oSel as object) 'set zero to the empty cells in the selection
    dim i&, o as object, j&, k&, data as object, p(), oCells as object
    oCells=oSel.queryEmptyCells 'ranges with empty cells
        for i=0 to oCells.Count-1
            o=oCells.getByIndex(i) 'one empty range
            data=o.getDataArray() 'data array from the empty range
            p=data 'help array
            for j=lbound(data) to ubound(data) 'set zeroes to the help array
                for k=lbound(data(j)) to ubound(data(j))
                    p(j)(k)=0 'set zero to the help array
                next k
            next j
            o.setDataArray(data) 'set help array to the empty range
        next i
End Sub
edit flag offensive delete link more

answered 2021-06-05 15:39:42 +0200

keme gravatar image

For some situations it may be easier to do it "backwards". Try this:

  • Fill a suitable area of a new sheet with all zeroes.
  • Copy the range where you need a "zero fill"
  • Paste into the zero-range using "Paste special" (ctrl+shift+V) with "Skip empty cells" ticked
  • Copy and paste back to where you needed that zero fill.

If you don't have any "context" (formulas, filters or other mechanisms referencing the range), the last "paste back" step may be unneeded.

edit flag offensive delete link more


I think that it is a bit easier this way:

  • Select the range with data
  • Copy
  • Type 0 and press Alt+Enter¹

Paste into the zero-range using "Paste special" (ctrl+shift+V) with "Skip empty cells" ticked

¹ This will fill the range with numericall zeroes; this it is not possible with irregular ranges.

LeroyG gravatar imageLeroyG ( 2021-06-06 03:00:01 +0200 )edit

answered 2021-06-05 15:25:06 +0200

karolus gravatar image

Ok, after first search&replace, the textual zeros already selected, do another search&replace:

search: .*
replace &
with Options: [x]regular Expression and [x]Selection only

edit flag offensive delete link more


If the cells were formatted to type Text then that first needs to be changed to a numeric format before attempting the Find&Replace, else that just re-enters the content as text.

erAck gravatar imageerAck ( 2021-06-05 22:53:07 +0200 )edit

answered 2021-06-05 22:49:33 +0200

erAck gravatar image

The '0 indicates you had the cells formatted to type Text already. See this FAQ; in short, apply a numeric (or General) number format first and then re-enter or replace the values.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-06-05 11:12:19 +0200

Seen: 106 times

Last updated: Jun 06