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

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

First time here? Check out the FAQ!

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

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

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

0

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

0

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
oDoc=ThisComponent
oSel=oDoc.CurrentSelection
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
setZero(o)
next i
elseif oSel.supportsService("com.sun.star.sheet.SheetCellRange") then 'one range in selection
setZero(oSel)
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
```

0

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.

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.

0

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`

0

`'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.

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

Seen: **106 times**

Last updated: **Jun 06**

How do I export a chart in an image format from LibreOffice Calc? [closed]

Are there plans for a "papercut" project for libreoffice [closed]

Is it normal for Calc goal seek to take very long? [closed]

Please refine "Search" in Calc - implement functions in Gnumeric [closed]

LibreOffice Calc will not link to external data via internet [closed]

Is there a LibreOffice .odt, .ods viewer for Android? [closed]

Why is Calc so much slower at opening/saving files than MS-Office? (win7 x64) [closed]

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.

thereis no way to

`find empty cells`

in calc -search&replace!What exactly is

`It`

?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?