2

In Excel, how can I find and replace cells with a value of #N/A with 0, blank, etc.? Finding works fine, but when using the replace tab in the dialog box, the option to search for values goes away and I can only search in formulas.

Improve this question
1
  • 2
    If you no longer need the formulas to remain live, you can strip them out to leave values/results only. Once you do that, you can find and replace the #N/A values.
    – PFitz
    Oct 22, 2017 at 21:25

2 Answers 2

Reset to default
3

You can always use IFERROR(). This will replace any errors with whatever value you choose. Just be mindful that it would also replace all other errors such as #VALUE etc. Use it like this:

IFERROR(yourformula, 0)

Alternatively you can use ISNA() to filter only for #N/A values, but it requires if functions and repetition of your formula which can get cluttered:

IF(ISNA(yourformula),0,yourformula)
Improve this answer
1

#N/A is normally the result of a formula. You either need to correct / extend the formula (to give blank instead of #N/A), or replace the formula. Replacing the result of a formula makes no sense.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .