Nest Existing Formulas in a new one (IFERROR)

Question

I have made bunch tables in Excel, everthing is great except fo the fact that once in a while one or two formulas outputs some errors( for valid reasons such as division by zero which in my case is fine). These errors get in my way when they become input to another formula that doesn't handle errors well( ex. SUMPRODUCT).

So I wanted to know if there is a way for me to nest my existing formulas in IFERROR( EXISTINGFORMULA, "-"). I don't want to go into each cell separetely and spend bunch of time typing in the same code.

NOTE:

-I should note that I am familiar with Regex, but I wasn't able to findout how can I apply it in Excel, because that would be my usual solution in software that knows how to use them.

-I do not have Admin right to my computer, so I prefer(if possible) to not install anything to achieve this goal.

Though I don't think Regex is the way to go for this, [here is a link](http://stackoverflow.com/q/22542834/2521004) to help you out with how to use it in Excel for future endeavors. Cheers — Automate This, Nov 26 '14 at 00:22

score 0 · Answer 1 · answered Aug 07 '14 at 13:31

You can use a macro to make the job:

k = 0
For i = 1 To 9999
    For e = 1 To 9999
        If Cells(i, e).Formula = "" Then
            k = k + 1
            If k > 5 Then Exit For
        Else
            k = 0
        End If
        If Left(Cells(i, e).Formula, 8) = "=IFERROR" Or Left(Cells(i, e).Formula, 1) <> "=" Then
        Else
            Cells(i, e).Formula = "=IFERROR(" & Mid(Cells(i, e).Formula, 2) & ",""-"")"
        End If
    Next
    If k > 20 Then Exit For
Next

If you prefer you can limit the range (modify the value of var i & e) where the macro work.

score 0 · Answer 2 · answered Oct 08 '16 at 01:47

I was looking to do the same thing. Since all my formulas were the same I really just wanted to add the IFERROR( to the front and the , "") ending to the back. I was able to do it by selecting my cells and searching for = and replacing all with nothing. This made the the formulas into text and I could continue using find and replace all without excel whining about improper formulas.

Next I searched for INDEX since it was first in my existing formula and replacing all with IFERROR(INDEX

Then I searched for )) as that was the end of my existing formula and replaced all with )), "") since I wanted nothing to show if there was an error.

Finally I searched for IFERROR and replaced all with =IFERROR to turn them all into formulas again. It worked amazingly well.

I know it's not fancy code or anything but I thought I would share what worked for me since this site has helped me so many times before.

Nest Existing Formulas in a new one (IFERROR)

2 Answers2