**QUESTION :**

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:

- Find and replace all the
`=`

at the beginning of the formula with`#`

– this would convert the formula in plain text - 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%)`

- 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.

**ANSWER :**

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.

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.

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),"")
```