How to Replace Text in Excel Formula (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, you may find it necessary to replace text in Excel formulas to change the previously written formulas immediately. If you are looking for the easiest ways of doing this task, then you are in the right place. So, let’s get started with the article.


Replace Text in Excel Formula: 7 Methods

Here, we have two formulas in the “Discounted Price” column and “>2000 or not” column, and we will show how to change the text string or numeric string in these formulas.

We have used the Microsoft Excel 365 version here; you can use any other version according to your convenience.

replace text in excel formula


Method-1: Replace Text in Excel Formula Manually

Here, we have used a formula with the IF function and got Yes for the prices that are greater than 2000. Now, we want to replace “Yes” with “Greater than 2000” in the formula manually.

replace text in excel formula

Steps:
➤ Select the first cell of the column “>2000 or not”.
So, it shows the formula of this cell in the formula bar.

replacing manually

➤ Replace “Yes” with “Greater than 2000” in the formula bar manually.

replacing manually

➤ Press ENTER and drag down the Fill Handle tool.

replacing manually

Result:
In this way, you will be able to replace “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 Replace Option to Replace Text in Excel Formula 

In this section, we will use the Replace option to replace the text “Yes” with “Greater than 2000” in the formula of the “>2000 or not” column.

replace text in excel formula

Steps:
➤ Select the cells of the “>2000 or not” column.
➤ Go to Home Tab >> Editing Group >> Find & Select Dropdown >> Replace Option.

You can use the shortcut key CTRL+H also instead of this procedure.

replace option

After that, the Find and Replace dialog box will appear.
➤ Write and select the following:

Find what → Yes
Replace with → Greater than 2000
Within → Sheet
Search → By Rows
Look in → Formulas

➤ Select the Replace All option.

replace option

Then, a message box will appear which says, “All done. We made 9 replacements.”

replace option

Result:
Afterward, you will be able to replace “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 Go to Special Option to Replace Text in Excel Formula 

You can replace the text “Yes” with “Greater than 2000” in the formula of the “>2000 or not” column by using the Go to Special option.

replace text in excel formula

Steps:
➤ Go to Home Tab >> Editing Group >> Find & Select Dropdown >> Go To Special Option.

Go To Special option

Then, the Go To Special wizard will open up.
➤ Select the Formulas option and press OK.

Go To Special option

After that, the cells of the “>2000 or not” column will be selected.

Go To Special option
➤ Follow Method-2 and you will get the new formula with text “Greater than 2000” instead of “Yes”.

Go To Special option


Method-4: Using a Shortcut Key to Replace Text in Excel Formula 

Here, we will use a shortcut key to easily replace text in the following formula.

replace text in excel formula

Steps:
➤ Press CTRL+TILDE key (the key above the TAB key and below the ESC key).
Then, it will show the formulas used in the “>2000 or not” column.

shortcut key

Now, follow Method-2, and you will get the new formulas with a text “Greater than 2000” instead of “Yes”.

shortcut key

➤ Press CTRL+TILDE key once again.
After that, you will get the new results due to the change of the formula in the “>2000 or not” column.

shortcut key


Method-5: Using a VBA Code 

In the Discounted Price column, we have the discounted prices after using a formula with a discount rate of 0.06, and now we want to replace this discount rate with 0.04 by changing this value in the formula. To do this here, we will use a VBA code.

replace text in excel formula

Step-01:
➤ Go to Developer Tab >> Visual Basic Option.

VBA code

Then, the Visual Basic Editor will open.
➤ Go to Insert Tab >> Module Option.

VBA code

After that, a Module will be created.

VBA code

Step-02:
➤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.

Result:
In this way, you will be able to replace 0.06 with 0.04 in the formulas of the Discounted Price column.

replace text in excel formula


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

Here, we will use the SUBSTITUTE function and the FORMULATEXT function along with a VBA code to replace 0.06 with 0.04 in the formulas of the Discounted Price column, and then we will get new prices in the New Price column. For additional calculation, we have added a new column Formula.

replace text in excel formula

Step-01:
➤ Use the following formula in the cell E5.

=SUBSTITUTE(FORMULATEXT(D5),0.06,0.04)

Here, D5 is the value of the Discounted Price column.

  • FORMULATEXT(D5) → returns the used formula in cell D5
    Output → C5-C5*0.06
  • SUBSTITUTE(FORMULATEXT(D5),0.06,0.04) becomes
    SUBSTITUTE(C5-C5*0.06,0.06,0.04) → replaces 0.06 with 0.04
    Output → C5-C5*0.04

SUBSTITUTE and FORMULATEXT function

➤ Press ENTER.
➤ Drag down the Fill Handle tool.

SUBSTITUTE and FORMULATEXT function

After that, we have got our new formulas in the Formula column, which we want to use to get the new prices in the New Price column.

SUBSTITUTE and FORMULATEXT function

To do this, we have to use a VBA code to create a function first.

Step-02:
➤ Follow Step-01 of Method-5
➤ Write the following code:

Function EVAL(value As String)

Application.Volatile
EVAL = Evaluate(value)

End Function

VOLATILE recalculates whenever a calculation occurs in any cells on the worksheet, and this VBA code will create a function named EVAL.

SUBSTITUTE and FORMULATEXT function

➤ After saving the code, return to the worksheet.
➤ Type the created function name in cell F5.

=EVAL(E5)

EVAL will return to us the value of the formula in cell E5.

SUBSTITUTE and FORMULATEXT function

➤ Press ENTER and drag down the Fill Handle tool.

SUBSTITUTE and FORMULATEXT function

Result:
After that, you will be able to replace 0.06 with 0.04 in the formulas of the New Price column.

replace text in excel formula


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

In this section, we will use the REPLACE function and the FORMULATEXT function along with a VBA code to replace 0.06 with 0.04 in the formulas of the Discounted Price column, and then we will get new prices in the New Price column.

replace text in excel formula

Step-01:
➤ Use the following formula in the cell E5.

=REPLACE(FORMULATEXT(D5),FIND("*",FORMULATEXT(D5),1)+1,4,0.04)

Here, D5 is the value of the Discounted Price column.

  • FORMULATEXT(D5) → returns the used formula in cell D5
    Output → C5-C5*0.06
  • FIND(“*”,FORMULATEXT(D5),1) → becomes
    FIND(“*”, C5-C5*0.06,1) → finds the position of the sign “*”
    Output → 7
  • FIND(“*”,FORMULATEXT(D5),1)+1 → adds up 1 with the position of the sign “*”
    Output → 8
  • REPLACE(FORMULATEXT(D5),FIND(“*”,FORMULATEXT(D5),1)+1,4,0.04) becomes
    REPLACE(C5-C5*0.06,FIND(“*”,8,4,0.04) → replaces 0.06 with 0.04
    Output → C5-C5*0.04

REPLACE and FORMULATEXT function

➤ Press ENTER.
➤ Drag down the Fill Handle tool.

REPLACE and FORMULATEXT function

After that, we have got our new formulas in the Formula column, which we want to use to get the new prices in the New Price column.

REPLACE and FORMULATEXT function

To do this, we will use our created function EVAL in the previous method.

Step-02:
➤ Type the following formula in cell F5.

=EVAL(E5)

EVAL will return us the value of the formula in cell E5.

REPLACE and FORMULATEXT function

➤ Press ENTER.
➤ Drag down the Fill Handle tool.

REPLACE and FORMULATEXT function

Result:
Finally, you will be able to replace 0.06 with 0.04 in the formulas of the New Price column.

replace text in excel formula


Practice Section

For doing practice by yourself, we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Download Workbook


Conclusion

In this article, we tried to cover some of the ways to replace text in Excel formulas. Hope you will find it helpful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles


<< Go Back to Find and Replace | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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