Add a Function to Existing Formula

I would like to be able to amend my already existing formulas and add them new functions in mass. For example, I originally wrote a calculation

=Sheet1!D38-Sheet1!E38

and would like to add an =IFERROR so it looks like this:

=IFERROR(Sheet1!D38-Sheet1!E38, 0%)

which is relatively easy to do manually when you have one or two of them but I have a whole document with formulas to be amended.

Is there an easy way of adding a new function to existing formulas without going through all of them one by one?

The presented example is random; I might have to do it with longer and more complex formulas like =COUNTIFS

The quick fix I have tried before consisted on:

1. Find and replace all the = at the beginning of the formula with # - this would convert the formula in plain text
2. Copy and paste the second part of the new formula, the one that would go at the end, in every single cell. In this case it would be , 0%)
3. Substitute the # with =IFERROR( to have the formula back, with the additional information I wanted

But this is not a very efficient way of doing it.

1

4 Answers

You can substitute in 2 steps, without manual editing:
1- search&replace = with =IFERROR(
(this will produce a lot of errors)
2- search&replace the line end with ,0%)
Enter the following 2 keystrokes for the line end:
Control-M
Control-J

Note that you won't see any character on entry but it'll work.

3

I generally use regex add-in to manipulate text with more flexibility than built in search / replace.

It provides both find / replace and also worksheet functions with regular expressions.

In your example you need to

• look for: =(.*)
• replace to: =iferror($1,0%)

Note that this add-in doesn't have the functionality to replace only in selection, so you need to take care to write specific enough look for expression.

2

Say we have a group of cells we which to edit en-mass:

We select the cells and run this short macro:

Sub FixFormula()
'
' hi-light the cells you wish to process
' Dim r As Range, rngF As Range, s As String Set rngF = Selection.Cells.SpecialCells(xlCellTypeFormulas) For Each r In rngF s = "(" & Mid(r.Formula, 2) & ")" r.Formula = "=IFERROR(" & s & ","""")" Next r
End Sub

and all the formulas will convert from:

=A1/B1

to:

=IFERROR((A1/B1),"")

A temporary solution to demonstrate a possible fix; see image. But stipulations on my comment.

Copy/Paste formula, Click and Hold method.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy