How do I replace numbers in a value NOT a formula?

I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show up
as values RESULTING from formulas?

Change the formulas !

OR

If you don't need the formulas for any *further* calculations,
Select the formulas,
right click in the selection,
Choose "Copy",
Right click again and choose "Paste Special",
Click on "Values", then <OK>.

Now you can edit and replace the 0's with blanks,

Which is *EXACTLY* the same as *DELETING* the formulas in the first
place.<g>

OR

Change the formulas !
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Riverrat" <[email protected]> wrote in message
news:[email protected]...
I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show up
as values RESULTING from formulas?

If you simply don't want to see anything where the cell value is zero, you
could use Tools / Options, select the View tab and clear the 'zero values'
checkbox.

"Riverrat" wrote:

> I want to replace zeros with blanks. In the Find tab of the Find/Replace
> dialog box I can choose to find the zeros in values as opposed to formulas.
> However, when I go to the Replace tab, the only option it gives me is to
> replace zeros found in formulas. How can I replace only zeros that show up
> as values RESULTING from formulas?

You can't by using find and replace, you can however do it in the same
formula that might return a zero

=IF(yourformula=0,"",yourformula)

note that if you do calculation using an operand the result will be a value
error since the blank is text,
if you are plotting a graph then you should use the NA() function instead

=IF(yourformula=0,NA(),yourformula)

you might then want to hide the result since it will look like #N/A! so you
can use conditional formatting, formula is

=ISNA(A2)

(where A2 is the cell that holds the original formula), format with white
fonts

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Riverrat" <[email protected]> wrote in message
news:[email protected]...
>I want to replace zeros with blanks. In the Find tab of the Find/Replace
> dialog box I can choose to find the zeros in values as opposed to
> formulas.
> However, when I go to the Replace tab, the only option it gives me is to
> replace zeros found in formulas. How can I replace only zeros that show
> up
> as values RESULTING from formulas?

I'll need the formulas possibly later so I'd rather not change them.
Otherwise I'd do just as you suggested.

"RagDyeR" wrote:

> Change the formulas !
>
> OR
>
> If you don't need the formulas for any *further* calculations,
> Select the formulas,
> right click in the selection,
> Choose "Copy",
> Right click again and choose "Paste Special",
> Click on "Values", then <OK>.
>
> Now you can edit and replace the 0's with blanks,
>
> Which is *EXACTLY* the same as *DELETING* the formulas in the first
> place.<g>
>
> OR
>
> Change the formulas !
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Riverrat" <[email protected]> wrote in message
> news:[email protected]...
> I want to replace zeros with blanks. In the Find tab of the Find/Replace
> dialog box I can choose to find the zeros in values as opposed to formulas.
> However, when I go to the Replace tab, the only option it gives me is to
> replace zeros found in formulas. How can I replace only zeros that show up
> as values RESULTING from formulas?
>
>
>

Thank you for the suggestion--it opened my eyes to a view option I wasn't
previously aware of--but I'll be taking averages of some of these rows
containing zeros. If the zeros are present but I'm not aware of them because
I turned them off, my averages will be skewed low.

"bpeltzer" wrote:

> If you simply don't want to see anything where the cell value is zero, you
> could use Tools / Options, select the View tab and clear the 'zero values'
> checkbox.
>
> "Riverrat" wrote:
>
> > I want to replace zeros with blanks. In the Find tab of the Find/Replace
> > dialog box I can choose to find the zeros in values as opposed to formulas.
> > However, when I go to the Replace tab, the only option it gives me is to
> > replace zeros found in formulas. How can I replace only zeros that show up
> > as values RESULTING from formulas?

Do I add this "IF" formula to the original formula or can I somehow overlay
it on the cells containing the original formula?
It seems your suggestion is the best so far for my needs.

"Peo Sjoblom" wrote:

> You can't by using find and replace, you can however do it in the same
> formula that might return a zero
>
> =IF(yourformula=0,"",yourformula)
>
> note that if you do calculation using an operand the result will be a value
> error since the blank is text,
> if you are plotting a graph then you should use the NA() function instead
>
> =IF(yourformula=0,NA(),yourformula)
>
> you might then want to hide the result since it will look like #N/A! so you
> can use conditional formatting, formula is
>
> =ISNA(A2)
>
> (where A2 is the cell that holds the original formula), format with white
> fonts
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "Riverrat" <[email protected]> wrote in message
> news:[email protected]...
> >I want to replace zeros with blanks. In the Find tab of the Find/Replace
> > dialog box I can choose to find the zeros in values as opposed to
> > formulas.
> > However, when I go to the Replace tab, the only option it gives me is to
> > replace zeros found in formulas. How can I replace only zeros that show
> > up
> > as values RESULTING from formulas?
>
>

Peo is saying the same thing I said, but using different words:

*Change your formulas !*

Post back with a formula you're using, and we'll gladly show you how to
revise it to eliminate the 0's.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Riverrat" <[email protected]> wrote in message
news:[email protected]...
Do I add this "IF" formula to the original formula or can I somehow overlay
it on the cells containing the original formula?
It seems your suggestion is the best so far for my needs.

"Peo Sjoblom" wrote:

> You can't by using find and replace, you can however do it in the same
> formula that might return a zero
>
> =IF(yourformula=0,"",yourformula)
>
> note that if you do calculation using an operand the result will be a
value
> error since the blank is text,
> if you are plotting a graph then you should use the NA() function instead
>
> =IF(yourformula=0,NA(),yourformula)
>
> you might then want to hide the result since it will look like #N/A! so
you
> can use conditional formatting, formula is
>
> =ISNA(A2)
>
> (where A2 is the cell that holds the original formula), format with white
> fonts
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "Riverrat" <[email protected]> wrote in message
> news:[email protected]...
> >I want to replace zeros with blanks. In the Find tab of the Find/Replace
> > dialog box I can choose to find the zeros in values as opposed to
> > formulas.
> > However, when I go to the Replace tab, the only option it gives me is to
> > replace zeros found in formulas. How can I replace only zeros that show
> > up
> > as values RESULTING from formulas?
>
>

Exactly as Peo said

=IF(yourformula=0,"",yourformula)

Take your formula, whatever it is, EXCEPT the = sign, and put it in the
formula above in both places where you can see it say yourformula, eg in
cell A2 lets assume you have the following

=SUM(B2:Z2)

Simply make it

=IF(SUM(B2:Z2)=0,"",SUM(B2:Z2))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Riverrat" <[email protected]> wrote in message
news:[email protected]...
> Do I add this "IF" formula to the original formula or can I somehow
> overlay
> it on the cells containing the original formula?
> It seems your suggestion is the best so far for my needs.
>
> "Peo Sjoblom" wrote:
>
>> You can't by using find and replace, you can however do it in the same
>> formula that might return a zero
>>
>> =IF(yourformula=0,"",yourformula)
>>
>> note that if you do calculation using an operand the result will be a
>> value
>> error since the blank is text,
>> if you are plotting a graph then you should use the NA() function instead
>>
>> =IF(yourformula=0,NA(),yourformula)
>>
>> you might then want to hide the result since it will look like #N/A! so
>> you
>> can use conditional formatting, formula is
>>
>> =ISNA(A2)
>>
>> (where A2 is the cell that holds the original formula), format with white
>> fonts
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> Northwest Excel Solutions
>>
>> www.nwexcelsolutions.com
>>
>> (remove ^^ from email address)
>>
>> Portland, Oregon
>>
>>
>>
>>
>> "Riverrat" <[email protected]> wrote in message
>> news:[email protected]...
>> >I want to replace zeros with blanks. In the Find tab of the
>> >Find/Replace
>> > dialog box I can choose to find the zeros in values as opposed to
>> > formulas.
>> > However, when I go to the Replace tab, the only option it gives me is
>> > to
>> > replace zeros found in formulas. How can I replace only zeros that
>> > show
>> > up
>> > as values RESULTING from formulas?
>>
>>