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

T

Turk

Dear all,

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

Thanks


Turk
 
M

Max

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

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
 
M

Max

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

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
 
T

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
 
T

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
 
M

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
 
Top