How do I find and replace "values" (like #N/A) in a worksheet?

H

hdc

When I use V-lookup, I often have numerous results that appear as "#N/A". I
would like to be able to delete all of the "#N/A" values, or replace them
with a 0, withour have to individually select and delete them.
 
D

Dave Peterson

If you've already converted to values, then:
select your range
edit|Replace
and replace all.

If you want to adjust your =vlookup() formula:

=if(iserror(vlookup()),"",vlookup())
or
=if(iserror(vlookup()),0,vlookup())
 
J

JustinLabenne

I made a small utility to do this programmatically. Assuming your
formula returns correct results and has correct syntax, this utility
adds an ISERROR OR ISNA to cell error values. It also allows for
specifying text or characters to be used in the errors place, and for
the ISEROR and ISNA to be programmatically removed from the formula

You can download it here

http://jlxl.net/Excel/downloads.html
 
Top