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.
Excel Find and Replace #N/A
find and replacemicrosoft excel
Related Solutions
I would suggest the commercial product PowerGREP :
PowerGREP is a powerful Windows grep tool. Quickly search through large numbers of files on your PC or network, including text and binary files, compressed archives, MS Word documents, Excel spreadsheets, PDF files, OpenOffice files, etc. Find the information you want with powerful text patterns (regular expressions) specifying the form of what you want, instead of literal text. Search and replace with one or many regular expressions to comprehensively maintain web sites, source code, reports, etc. Extract statistics and knowledge from logs files and large data sets.
Changing the order is fine, so long as every value in the sheet is going to be replaced. If your sheet contained 11 though that wasn't going to be replaced then changing the order will still replace it with hello1.
To use match entire cell specify
,LookAt:=xlWhole
as another parameter to the Replace method. Replace is a valid method on a Range so your For loop is not necessary. You can instead specify
Range("A1:A1000").Replace What:="12", Replacement:="goodbye", LookAt:=xlWhole
Range("A1:A1000").Replace What:="4", Replacement:="cat", LookAt:=xlWhole
Best Answer
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: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: