View code for removal of unwanted text in cells
 

How can I make a cell blank when it has a certain phrase such as #DIV/0! or
N/A. I have a lot of these and it would be much better without them. I
prefer a blank cell. Any ideas out there?
--

View code for removal of unwanted text in cells
 

Hi

=IF(ISERROR(A2/A1),"",A2/A1)

where you replace A2/A1 with your formula.

Regards,
Per

On 14 Aug., 22:10, Doug wrote:
How can I make a cell blank when it has a certain phrase such as #DIV/0! or
N/A. *I have a lot of these and it would be much better without them. I
prefer a blank cell. Any ideas out there?
--

View code for removal of unwanted text in cells
 

Either include an IF statement like
=IF(ISERROR(YourFunction),"",YourFunction)
or
=IF(ISNA(YourFunction),"",YourFunction)

or, if you truly want to delete them, you can use F5 - Special - Formulas -
Error, and then press delete (but I don't recommend this!)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Doug" wrote:

How can I make a cell blank when it has a certain phrase such as #DIV/0! or
N/A. I have a lot of these and it would be much better without them. I
prefer a blank cell. Any ideas out there?
--

View code for removal of unwanted text in cells
 

Proper functions will clean that up for you...

check your denominator before dividing to eliminate Div by Zero
=if(A1 = 0, 0, A2/A1)

=if(countif(A1:A10, "MyValue") = 0, "Not found", vlookup("MyValue", A1:B10,
2,false))
clears up N/A errors.

The one thing I would add is try to be specific in which error you are
catching. You can use IsError but that cathces everything which is
potentially dangerous. If a cell is deleted or such leading to a #ref error
you do not want down stream formulas to ignore that error and return a value
that is not valid. Better to show the error value than the wrong value.
--
HTH...

Jim Thomlinson


"Doug" wrote:

How can I make a cell blank when it has a certain phrase such as #DIV/0! or
N/A. I have a lot of these and it would be much better without them. I
prefer a blank cell. Any ideas out there?
--

View code for removal of unwanted text in cells
 

I am getting the #DIV/0! based on some of the cells with N/A. Apparently it
views the N/A as a zero and gives #DIV/0! for the formula in those cells. Is
there a VBA that I can enter into the sheet view code for this? If not where
do I insert the formulas that you wrote?
--
Thank you!


"Jim Thomlinson" wrote:

Proper functions will clean that up for you...

check your denominator before dividing to eliminate Div by Zero
=if(A1 = 0, 0, A2/A1)

=if(countif(A1:A10, "MyValue") = 0, "Not found", vlookup("MyValue", A1:B10,
2,false))
clears up N/A errors.

The one thing I would add is try to be specific in which error you are
catching. You can use IsError but that cathces everything which is
potentially dangerous. If a cell is deleted or such leading to a #ref error
you do not want down stream formulas to ignore that error and return a value
that is not valid. Better to show the error value than the wrong value.
--
HTH...

Jim Thomlinson


"Doug" wrote:

How can I make a cell blank when it has a certain phrase such as #DIV/0! or
N/A. I have a lot of these and it would be much better without them. I
prefer a blank cell. Any ideas out there?
--