It gives always a textual 0 ('0) as replacement!
thereis no way to
find empty cells in calc -search&replace!
Itgives always a textual 0 ('0) as replacement!
What exactly is
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?
Ok, after first search&replace, the
textual zeros already selected, do another search&replace:
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.
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” (
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
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 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.
Combining Karolus, LeroyG and erAck I found the following easy solution with two times search and replace:
empty(=nothing) and replace with
After that only the cells with a replaced
'0are still selected (even in an irregular range) and now “abracadabra”…
- with the same search and replace-box, but search
empty) and replace with the same
[x]Regular expression and
DONE…Works also for an other number…
Thanks very very much (sorry, I’m not allowed to give you upvotes).
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