Find #N/A and replace it

[paulo_bajasaeus]

Hi. I have a table of about 25 columns and 250 rows of data, wich is the result of various formulas like vlookup. I need to find and replace the #N/A output of the formulas for a blank space, i need nothing to appear in those celds. When using the find function i cant get it to work, of course, isnce is the output of the formula. I cant also copy and paste as especial (values only) so i can do this, since i need the formulas to stay.

Plz i need your help!

Thanks in advance to all of you!

Paulo

[NBVC]

The best way to fix that is to amend your formulas to not show the #N/A in the first place.

e.g. =IF(ISNA(your_formula),"",your_formula)

The ISNA() function returns TRUE if the result of your formula is #N/A.. and therefore you can get it to return a null space instead.

[ChemistB]

There might be a way with VBA but without it, you'll need to amend all your formulas to begin with

Please Login or Register  to view this content.

You might also, by column, do an autofilter on #NA and then replace all of those using control Enter.

ChemistB

[paulo_bajasaeus]

Hi! how can i make it doesnt put anything on the celd? because if i put a "" it will then not add the celd with another...

Im doing the ISBLANK funtion to check out if the celds in which the ISNA function comes true, and it turns FALSE, so they are not blank.

Sorry for all the hassle,

Thanks in advance!

[NBVC]

Instead of using ISBLANK() function... Say, =""

e..g instead of =Isblank(A1) use =A1=""

[philibe]

I'm new to Excel and the like, so this might not be what you need, but there is a way of making it appear like the cells are blank.

If you highlight the first cell in a column you want to change, go to

Format -> Conditional Formatting -> Formula Is

=ISERROR(Cell#)

delete the $

and Format -> change the background colour to white.

Copy formatting across cells