Simple excel find and replace for formulas

问题:

I have numerous cells all over the place on a worksheet that look like =((E9-E8)/E8). I want to use the first two values to go into this new formula, (EXP((LN(E9/E8)/14.32))-1).

How can I change them all to the new formula in one fell swoop?

回答1:

If the formulas are identical you can use Find and Replace with Match entire cell contents checked and Look in: Formulas. Select the range, go into Find and Replace, make your entries and `Replace All.

Or do you mean that there are several formulas with this same form, but different cell references? If so, then one way to go is a regular expression match and replace. Regular expressions are not built into Excel (or VBA), but can be accessed via Microsoft's VBScript Regular Expressions library.

The following function provides the necessary match and replace capability. It can be used in a subroutine that would identify cells with formulas in the specified range and use the formulas as inputs to the function. For formulas strings that match the pattern you are looking for, the function will produce the replacement formula, which could then be written back to the worksheet.

Function RegexFormulaReplace(formula As String)
    Dim regex As New RegExp
    regex.Pattern = "=\(\(([A-Z]+\d+)-([A-Z]+\d+)\)/([A-Z]+\d+)\)"
'   Test if a match is found
    If regex.Test(formula) = True Then
        RegexFormulaReplace = regex.Replace(formula, "=(EXP((LN($1/$2)/14.32))-1")
    Else
        RegexFormulaReplace = CVErr(xlErrValue)
    End If
    Set regex = Nothing
End Function

In order for the function to work, you would need to add a reference to the Microsoft VBScript Regular Expressions 5.5 library. From the Developer tab of the main ribbon, select VBA and then References from the main toolbar. Scroll down to find the reference to the library and check the box next to it.

回答2:

It turns out that the solution was to switch to R1C1 Cell Reference. My worksheet was structured in such a way that every formula had the same structure just different references. Luck though, they were always positioned the same way

=((E9-E8)/E8) 

became

=((R[-1]C-R[-2]C)/R[-2]C)

and

(EXP((LN(E9/E8)/14.32))-1)

became

=(EXP((LN(R[-1]C/R[-2]C)/14.32))-1)

In R1C1 Reference, every formula was identical so the find and replace required no wildcards. Thank you to those who answered!

回答3:

The way I typically handle this is with a second piece of software. For Windows I use Notepad++, for OS X I use Sublime Text 2.

1. In Excel, hit Control + ~ (OS X)
2. Excel will convert all values to their formula version
3. CMD + A (I usually do this twice) to select the entire sheet
4. Copy all contents and paste them into Notepad++/Sublime Text 2
5. Find / Replace your formula contents here
6. Copy the contents and paste back into Excel, confirming any issues about cell size differences
7. Control + ~ to switch back to values mode

回答4:

Use the find and replace command accessible through ctrl+h, make sure you are searching through the functions of the cells. You can then wildcards to accommodate any deviations of the formula. * for # wildcards, ? for charcter wildcards, and ~? or ~* to search for ? or *.

回答5:

You can also click on the Formulas tab in Excel and select Show Formulas, then use the regular "Find" and "Replace" function. This should not affect the rest of your formula.