How to format a cell to make it blank if it's content is "N/A"

[Turk]

Dear all,

How to format a cell to make it blank if it's content is "N/A"

Thanks


Turk

 

[Guest]

Use ISNA around your formula returning the N/As, eg:
=IF(ISNA(yourformula),"",yourformula)

 

[Turk]

Thank you Max,

However my formulae will be a bit too long if using your method, any
alternate way to do it please?

Turk

 

[Guest]

Well, I suppose conditional formatting using ISNA would provide an
alternative route to hide the ugly #N/As ..

Select the formulas col (assume this is in col A)
Then click Format > Conditional Formatting, and make the settings as:
Condition 1, Formula is: =ISNA(A1)
Format the font as white (to mask) > OK out

Note that the underlying values masked are still #N/A, though ..

 

[T. Valko]

You could use conditional formatting to hide them. They'll still be there,
you just won't be able to see them but if these results are used in other
calculations this could be a problem.

To use conditional formatting:

Select the range of cells in question. Assume this is A1:A10
Goto the menu Format>Conditional Formatting
Formula Is: =ISNA(A1)
Set the font color to be the same as the fill color
OK out

Biff

 

[Turk]

Thank you Valko,

Sorry that I can't blank the cells by using your method, my formula is
=Vlookup(.....),
if the ISNA(A1) command apply to Vlookup?

thanks again

Turk

 

[Turk]

Thank you Max,

Sorry that I can't blank the cells by using your method, my formula is
=Vlookup(.....),
if the ISNA(A1) command apply to Vlookup?

thanks again

Turk

 

[Max]

Maybe try the CF using instead:
Formula is: =ISERROR(A1)

ISNA traps only #N/A error returns
(the #N/A returns can be from vlookup, match, hlookup, etc)

ISERROR traps all errors