find/replace with *formula values*

[jneiblas]

I have multiple formulas on the sheet, many of which return a value of 0
I want to delete all the 0's from my sheet (or all the formulas that return that value)
I tried to enter "0" into find and replace but it does not work because technically the cell value is not 0
Help?

 

[AlanSidman]

Post a sample file showing the issue. 8-10 records should be sufficient. No pictures as they can not be manipulated.

 

[vletm]

jneiblas
Do You really want to delete some of formulas ?
Have You tested below?
From ... 0

To ..

 

[Peter Bartholomew]

I have multiple formulas on the sheet, many of which return a value of 0
I want to delete all the 0's from my sheet (or all the formulas that return that value)
I tried to enter "0" into find and replace but it does not work because technically the cell value is not 0
Help?

The normal practice is not to destroy the formulas but rather to modify them to return "" rather than 0. Instead of
= FUNCTION(data)
one might have
= IF(data<>"", FUNCTION(data), "")
The actual test depends upon the circumstances.

 

[deciog]

jneiblas

Another way is to format all the cells that you don't want result with zeros with custom formatting
Simple example 0; -0;; @ or not show zeros in the result in hours hh:mm;;""

Decio

 

[p45cal]

I tried to enter "0" into find and replace but it does not work because technically the cell value is not 0

Miss out the quote marks and look in Values:


You should also tick the Match entire cell contents box (I forgot to in the screen shot above).
click on Find All, then press Ctrl + A on the keyboard to select all found cells, then close the dialogue box. The selected cells remain selected. Then just press Delete on the keyboard.