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

[Patlab]

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

[karolus]

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

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

What exactly is It ?

[Patlab]

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?

[karolus]

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

[erAck]

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.

[keme]

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.

[LeroyG]

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.

[erAck]

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.

[Patlab]

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

[KamilLanda]

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

[Francewhoa]

In addition to the other good answers, this can also be done using the Calc RegEx ^$ on all cells, or a selected range, or a multiselection.

Using RegEx ^$, it is just 4 clicks or so to search for empty cells. And optionally replace them with any value to your liking.

The three steps to do this are in my other comment with screenshots at How to search/select empty cells? - #12 by Francewhoa

[guyr]

@Francewhoa I am able to use your method to find an empty cell in a range using the Search / Replace dialog, where I can click the checkbox for “Regular expressions”. Is there any way to accomplish the same thing using only the search bar at the bottom? The only options on the search bar are “Formatted Display” and “Match Case”. I tried entering the regular expression ^$, but I think the search bar is interpreting that as a text string. Thanks.

[erAck]

The Find Bar (Ctrl+F) doesn’t do regular expressions. Use the Find&Replace (Ctrl+H) dialog for that.