Hello,
I have a spreadsheet with data, some of the cells contain a single zero. My reason for wanting to delete 0’s is so that I can perform an average calculation where the cells with a single zero is not included in the calculation.
Is there a way to do this with find & replace rather than to use a formula and replicate the dataset?
Thank you.
Oliver
I solved it by copying the data to another sheet and running a formula (IF(B2=0,"",B2) and then copy & past it back.
Just for curiosity:
What’s meant by “a celll with a single zero” or “cells that have a single 0 value”?
Are there “non-single zeroes”?
And:
If cells have the value 0, I would ask by what they got this value. If I learn that that the 0 was a “valid value” (you may call it a “correct” or a “legal” one), the zeroes (0-values) must, of course, also be included when an average is calculated.
Otherwise they need to be removed and the process they were created by must be enhanced to avoid the creation of “invalid values” for the future.
ctrl-h (find and replace), find: ‘0’, check ‘entire cells’, replace: [leave empty], replace all …
With Search & Replace,
Find: 0
mark: Entire cells
Replace:
‘empty’
To mark Entire cells, regular expressions must be not selected.
First, thank you all for your answers.
@Lupp what I meant with “a cell with a single zero” was that there is a “0” in the sell as supposed to a valid entry, and yes the data-set I got is not correct in the sense that they have added a zero value in cells where there was no data collected. It’s a data-set from a government agency so go figure.
You surely will easily get that govetnment agency to enhance their process they collect and spread the data with. Good luck!
By the way: Non-governmental institutions (like banks e.g.) sometimes seem to like it even worse.