Excel Question - Find and Replace Errors with Text

Factual Questions
1

I am working with a spreadsheet with a couple of hundred rows/ cases. I am doing some basic calculations (division, averages, etc.). In some cases I am missing data, so I get errors (DIV/O, #VALUE!). I would like to use find and replace to replace those errors with text, like “Missing”. Is there a way to get find and replace to do this? In my initial attempts, find does not seem to recognize the errors. Google just tells me about all the many errors you can get with find an replace.

Thanks for anyone with knowledge of how to do this, or a solid explanation of why it can’t be done.

2

If your formula was e.g. A1/B1, replace the formula with:

=If(iserror(a1/b1),“text you want to replace error with”,a1/b1)

Do this for all cells where you have the problem (fill across, fill down)

Hope this helps.

BTW, I don’t think it can be done with find and replace because the find is searching on the formula not the resulting value.

3

I know how to do all of it can be much more involved than you might think. If you are just getting one or two types of errors, you can use Excel formulas to mark them as whatever you want like 'Missing" but if there are lots of different types or errors in the same column, it can be pretty tricky. I used to teach classes on this stuff on there are lots of different solutions.

You can’t just do a search and replace because Excel math is formula based and the results are not text in computer terms at all. A math result could potentially be affected by the change to any cell that is referenced so it has to be dynamic unless you want to force it not to be.

A few hundred rows isn’t very big for an Excel spreadsheet. Your choices are to design some elaborate formulas to handle all of your errors or to simply to convert the whole thing to text results once you are happy with what you have and do a true search and replace on the converted text. The latter is much, much faster but isn’t ideal if this is something that you will need to do routinely.

statij gave a reasonable solution if you just want only one error message returned that includes all errors but I couldn’t really tell how elaborate the problem was based on your OP.

4

Thanks all! Shagnasty, I think your suggestion to convert it all to text at the end is the way to go for this one.

5

Let me know if you have problems on that step. It is very fast. I went to Tulane where they taught us all about the basics of this type of thing but I realize that not everyone has that opportunity. :slight_smile:

6

You could try conditional formatting – that is if you just want to highlight the errors.
Filing that statij’s ise of teh IF formula does the trick. Somethiong I use all the time.