How to Replace Text in Excel Formula (7 Methods)

Scenario

We have two formulas in the Discounted Price column and >2000 or not column, and we want to demonstrate how to change the text string or numeric string in these formulas.

Method 1 – Manual Replacement

We have used the IF function in a formula and received Yes as the result for the prices that are greater than 2000.

If you want to replace Yes with Greater than 2000 in the formula manually:

• Select the first cell in the >2000 or not column.

• In the formula bar, replace Yes with Greater than 2000.

• Press ENTER and drag down the Fill Handle tool to apply the change to other cells.

You have successfully replaced Yes with Greater than 2000 in the formula.

Read More: How to Find and Replace Using Formula in Excel

Method 2 – Using the Replace Option

• Select the cells in the >2000 or not column.
• Go to the Home Tab, click on Editing, select Find & Select from the dropdown list and click on the Replace… Option (or use the shortcut key CTRL+H).

• In the Find and Replace dialog box, complete the following:
  • Find what: Yes
  • Replace with: Greater than 2000
  • Within: Sheet
  • Search: By Rows
  • Look in: Formulas
• Select the Replace All option.

A message box will appear saying: “All done. We made 9 replacements.”

You have successfully replaced Yes with Greater than 2000 in the formula.

Read More: Replace Text of a Cell Based on Condition in Excel

Method 3 – Using the Go to Special Option

• Go to the Home Tab, click on Editing, select Find & Select from the dropdown list and choose the Go To Special Option.

• Select the Formulas option and press OK.

The cells of the >2000 or not column will be selected.

• Follow the steps from Method 2 to replace Yes with Greater than 2000.

Method 4 – Using a Shortcut Key

• Press CTRL+TILDE key (the key above the TAB key and below the ESC key) to display formulas in the >2000 or not column.

• Replace Yes with Greater than 2000.

• Press CTRL+TILDE key again to see the updated results.

Method 5 – Using a VBA Code

In the Discounted Price column, replace the discount rate of 0.06 with 0.04 using VBA:

• Go to Developer Tab and select the Visual Basic Option.

• Insert a Module.

• Insert the following code in the module:

Sub replacestring()

Dim oldStr, newStr
oldStr = "0.06"
newStr = "0.04"

newStr = Replace(Range("D5,D6,D7,D8,D9,D10,D11,D12,D13") _
.Formula, oldStr, newStr)

Range("D5,D6,D7,D8,D9,D10,D11,D12,D13").Formula = newStr

End Sub

Here,

• • We have assigned our old value 0.06 in the oldStr variable, and 0.04 in the newStr variable and D5, D6, D7, D8, D9, D10, D11, D12, and D13 are the cells of our desired ranges.
  • REPLACE will replace 0.06 with 0.04 in the formulas of these cells and finally store these new values in the newStr variable.

• Press F5 to execute the code.

Method 6 – Using the SUBSTITUTE and FORMULATEXT Functions with a VBA Code

Step 1 – SUBSTITUTE Formula

• In cell E5, insert the following formula:

Here,

• • D5 represents the value in the Discounted Price column.
  • FORMULATEXT(D5) returns the formula used in cell D5 (e.g., “C5-C5*0.06”).
  • The SUBSTITUTE function replaces 0.06 with 0.04 in the formula.

• Press ENTER and drag down the Fill Handle tool to apply the formula to other cells.

The new formulas are in the Formula column and can be used to get the new prices in the New Price column.

Step 2 – Creating a VBA Function (EVAL)

• Follow the steps from Method 5 to create a VBA function named EVAL.
• Insert the following code:

Function EVAL(value As String)

Application.Volatile
EVAL = Evaluate(value)

End Function

• This function recalculates whenever any calculation occurs on the worksheet.

• Save the code and return to the worksheet.
• Insert the following in cell F5 to get the value of the formula in cell E5:
  

=EVAL(E5)