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? |
How do I replace numbers in a value NOT a formula?
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" wrote in message ... 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? |
How do I replace numbers in a value NOT a formula?
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? |
How do I replace numbers in a value NOT a formula?
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" wrote in message ... 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? |
How do I replace numbers in a value NOT a formula?
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" wrote in message ... 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? |
How do I replace numbers in a value NOT a formula?
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? |
How do I replace numbers in a value NOT a formula?
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" wrote in message ... 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? |
How do I replace numbers in a value NOT a formula?
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" wrote in message ... 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" wrote in message ... 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? |
How do I replace numbers in a value NOT a formula?
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" wrote in message ... 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" wrote in message ... 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? |
All times are GMT +1. The time now is 04:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com