0
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.
user3595828
Posted 2017-10-22T21:11:35.430
Reputation: 1
2
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)
ChP
Posted 2017-10-22T21:11:35.430
Reputation: 304
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.
Aganju
Posted 2017-10-22T21:11:35.430
Reputation: 9 103
1If 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. – P Fitz – 2017-10-22T21:25:58.737