Search and replace #N/A with null

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I wish to replace all cells containing #N/A (a value
returned by VLOOKUP) with a null string. I entered #N/A
and "#N/A" in the search field but in both cases I got a
message stating "Microsoft Excel cannot find the data
you're searching for." Any ideas?
 
Select the range, press F5, click special, assuming they are from formulas
check formulas, uncheck everything but errors, click OK
delete the formula from the formula bar and press ctrl + enter

Now this will replace the whole formula with an empty cell
If you want to avoid this directly you can replace the formula with

=IF(ISNA(MATCH(A1,B1:B100,0)),"",VLOOKUP(A1,B1:D100,3,0))



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
You can actually just press delete when you have select the errors

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top