Apache OpenOffice Community Forum

I have spreadsheets with blank cells and cells with "1"s. I need to replace the blank cells with zeros but find and replace won't let me use a blank field as a search term. Sure, I could set up parallel columns and use =if(A1="";0;1) and then replace the original columns with a Paste Special selecting numbers and then erase the columns with the formulas but that would take forever.
Any suggesting?

Well, I did find a solution. Apply the data filter to a column and select the condition as "=" and the value as "-empty-" but this has to be applied column by column. Any other ideas?

kozowh wrote: I could set up parallel columns and use =if(A1="";0;1) and then replace the original columns with a Paste Special selecting numbers and then erase the columns with the formulas but that would take forever.

On a slow machine it takes a few minutes for all the 1,048,576 cells of a column.

btw:
=if(A1="";0;1)
returns exactly the same as
=A1<>""

Thanks for the suggestion which appears to be simpler. However, I have only 500-10000 line per spreadsheet but about 60 columns. It would still be slow and laborious even though I have a fairly fast machine. Excel allows you to use blank as a search parameter in the search and replace function so all you have to do is highlight the target area and search for blank and replace with 0.

It would be great if the search and replace function had a drop down menu with -empty- as a choice as does the data filter function.

I had another idea. Use the hex code representation of a NUL which I would have guessed would be the same as -empty- but using \x0000 didn't work as the search key was not found. OK, I think I'm out of ideas now.

Download http://user.services.openoffice.org/en/ ... p?id=11048
Do NOT unzip and point Tools>Extensions... to this zip package.
Close the office or all spreadsheets at least.
Now you have a tool to select all blank cells if they are really blank and not "".
Tools>Add-Ons>SpecialCells>Contents...

insert a new sheet eg called result before the current one with your data in it (called yourData)
Go to the sheet after your data (called blanks for example)
In blanks A1 enter a zero
Highlight an area equivalent to the area your data occupies
Fill down then fill right
you now have a large block of cells containing a zero

Go to the results sheet
In cell A1 enter =yourData.A1+blanks.A1
Highlight an area equivalent to the area your data occupies
Fill down and then fill right

now you have a sheet showing a mix of ones and zeros

If you have text mixed into the area containing ones you will get a #VALUE! showing up - just edit the formula, where this happens and remove the +blanks.a1 (or whatever the formula is)

A bit messy but it works (at least on a small scale test)

Edit:

peterroots wrote:

Edit:

And your solution is the right one. My tool can select blanks but it takes another tool to fill a multiple selection.

Thanks very much for your suggestions. I'll be trying them soon. Regards, -W

Villeroy wrote:Download http://user.services.openoffice.org/en/ ... p?id=11048
Do NOT unzip and point Tools>Extensions... to this zip package.
Close the office or all spreadsheets at least.
Now you have a tool to select all blank cells if they are really blank and not "".
Tools>Add-Ons>SpecialCells>Contents...

Thanks for the selecting tool and it works great to highlight the blank cells.
But I don't know enough about Calc to figure out how I can paste zeros into those highlighted cells. Search and Replace still won't do it and paste won't work with irregularly shaped sections.

Can you give a quasi-noob some guidance here?

In an unused cell type a 0 and press enter, then Edit > Cut. Highlight the region where you want to convert blanks to zeros, then use Edit > Paste Special > Operations > Add > OK. This converts empty cells to zero, leaves numbers unchanged, and leaves text unchanged.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

MrProgrammer--
Very nifty solution which works well with adding zero and it doesn't disturb the text-containing cells. THANKS!!

dead neat solution - interestingly, in LibreOffice this only works if you edit>copy rather than edit>cut (or at least it does in 3.4.3 which I have just started trying out)

Villeroy wrote:Download http://user.services.openoffice.org/en/ ... p?id=11048
Do NOT unzip and point Tools>Extensions... to this zip package.
Close the office or all spreadsheets at least.
Now you have a tool to select all blank cells if they are really blank and not "".
Tools>Add-Ons>SpecialCells>Contents...

Hey, Villeroy. Whats your last verion of special cells? Ise SpecialCells.0.7.4.zip , but In LO 4.0 it doesnt work .
Do you have any updates? Ty.

Sorry, no. I won't install LibO 4 on any of my systems. So I can't make an extra version for it.