How to Replace Text in Excel Formula (7 Methods)

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.

replace text in excel formula


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.

replace text in excel formula

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

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

replacing manually

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

replacing manually

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

replacing manually

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

replace text in excel formula

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


Method 2 – Using the Replace Option:

replace text in excel formula

  • Select the cells in the >2000 or not column.
  • Go to the Home Tab > Editing Group > Find & Select Dropdown > Replace Option (or use the shortcut key CTRL+H).

replace option

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

replace option

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

replace option

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

replace text in excel formula

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


Method 3 – Using the Go to Special Option: 

replace text in excel formula

  • Go to the Home Tab > Editing Group > Find & Select Dropdown > Go To Special Option.

Go To Special option

  • Select the Formulas option and press OK.

Go To Special option

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

Go To Special option

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

Go To Special option


Method 4 – Using a Shortcut Key:

replace text in excel formula

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

shortcut key

  • Replace Yes with Greater than 2000.

shortcut key

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

shortcut key


Method 5 – Using a VBA Code:

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

replace text in excel formula

  • Go to Developer Tab > Visual Basic Option.

VBA code

  • Insert a Module.

VBA code

VBA code

  • Write the following code:
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.

VBA code

  • Press F5 to execute the code.

replace text in excel formula


Method 6 – Using SUBSTITUTE and FORMULATEXT Function with a VBA Code:

replace text in excel formula

Step 1 – SUBSTITUTE Formula:

  • In cell E5, use the following formula:
=SUBSTITUTE(FORMULATEXT(D5),0.06,0.04)
  • 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.

SUBSTITUTE and FORMULATEXT function

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

SUBSTITUTE and FORMULATEXT function

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

SUBSTITUTE and FORMULATEXT function

Step 2 – Creating a VBA Function (EVAL):

  • Follow the steps from Method 5 to create a VBA function named EVAL.
  • Write 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.

 

SUBSTITUTE and FORMULATEXT function

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

=EVAL(E5)

SUBSTITUTE and FORMULATEXT function

  • Press ENTER and drag down the Fill Handle tool to calculate values for other cells.

SUBSTITUTE and FORMULATEXT function

replace text in excel formula


Method 7 – Using REPLACE and FORMULATEXT Function with a VBA Code:

replace text in excel formula

Step 1 – REPLACE Formula:

  • In cell E5, use the following formula:
=REPLACE(FORMULATEXT(D5),FIND("*",FORMULATEXT(D5),1)+1,4,0.04)
  • FORMULATEXT(D5) returns the formula in cell D5.
  • FIND(“*”, FORMULATEXT(D5), 1) finds the position of the “*” sign.
  • The REPLACE function replaces 0.06 with 0.04 in the formula.

REPLACE and FORMULATEXT function

  • Press ENTER and drag down the Fill Handle tool.

REPLACE and FORMULATEXT function

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

REPLACE and FORMULATEXT function

Step 2 – Using EVAL Function:

  • Type =EVAL(E5) in cell F5 to get the value of the formula in cell E5.

REPLACE and FORMULATEXT function

  • Press ENTER and drag down the Fill Handle tool.

REPLACE and FORMULATEXT function

replace text in excel formula


Practice Section

We have provided a Practice section in a sheet named Practice.

practice


Download Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Find and Replace | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo