replacing zeros with blanks but leaving dates alone

Ok here is the problem;
I have a column that displays a date if no date exists
then a zero is placed in it. What I am trying to do is to clear (display
blank cell) that have a value of zero (0) but keep the date in all cells that
have a date. (I am aware of the fomula being in the blank cell but I already
know how to fix that by the paste special function)

I think the IF Function could do it, but when I've used suggested formulas
off this web page none seem to work. When I used this formula =IF(0,null,"")
then paste it to all cells, it removes all dates.



Any help would be appreciated

Thx

I can think of two options off the top of my head.

Easiest and quickest is to highlight the column and do a find/replace.

Find 0, replace with and leave it blank. Make sure it looks for entire
field, not any part.

Or.. in the formula, it would be =if(your formula = 0,"",your formula)

"Evil with a K" wrote:

Ok here is the problem;
I have a column that displays a date if no date exists
then a zero is placed in it. What I am trying to do is to clear (display
blank cell) that have a value of zero (0) but keep the date in all cells that
have a date. (I am aware of the fomula being in the blank cell but I already
know how to fix that by the paste special function)

I think the IF Function could do it, but when I've used suggested formulas
off this web page none seem to work. When I used this formula =IF(0,null,"")
then paste it to all cells, it removes all dates.



Any help would be appreciated

Thx

You need to tick the match entire cell contents on the find and replace.
1. CTRL +F
2. Find what = 0
3. Replace with = leave blank
4. tick match entire cell contents

This should work

Thanks
Matt





Evil with a K wrote:
Ok here is the problem;
I have a column that displays a date if no date exists
then a zero is placed in it. What I am trying to do is to clear (display
blank cell) that have a value of zero (0) but keep the date in all cells that
have a date. (I am aware of the fomula being in the blank cell but I already
know how to fix that by the paste special function)

I think the IF Function could do it, but when I've used suggested formulas
off this web page none seem to work. When I used this formula =IF(0,null,"")
then paste it to all cells, it removes all dates.

Any help would be appreciated

Thx

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

Your post is not entirely clear to me. The column with the dates/zeroes...
are the values manually entered or are they the result of formulas in that
column? If they are the result of formulas, then do this...

=IF(A1="","",YourFormulaWithoutTheEqualSign)

changing the A1 reference to the address of the first formula cell in
whatever column, of course.

If manually entered, then are you looking to change the values in the same
cells? If so, you can use Edit/Replace to do that.

--
Rick (MVP - Excel)


"Evil with a K" wrote in message
...
Ok here is the problem;
I have a column that displays a date if no date exists
then a zero is placed in it. What I am trying to do is to clear (display
blank cell) that have a value of zero (0) but keep the date in all cells
that
have a date. (I am aware of the fomula being in the blank cell but I
already
know how to fix that by the paste special function)

I think the IF Function could do it, but when I've used suggested formulas
off this web page none seem to work. When I used this formula
=IF(0,null,"")
then paste it to all cells, it removes all dates.



Any help would be appreciated

Thx