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

## 7 Methods to Replace Text in Excel Formula

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

We have used Microsoft Excel 365 version here; you can use any other version according to your convenience. ### Method-1: Replace Text in Excel Formula Manually

Here, we have used a formula with the IF function and got Yes for the prices which are greater than 2000. Now, we want to replace Yes with Greater than 2000 in the formula manually. Steps:
➤ Select the first cell of the column >2000 or not.
So, it is showing the formula of this cell in the formula bar. ➤ Replace Yes with Greater than 2000 in the formula bar manually. ➤ Press ENTER and drag down the Fill Handle tool. Result:
In this way, you will be able to replace Yes with Greater than 2000 in the formula. ### Method-2: Using Replace Option to Replace Text in Excel Formula

In this section, we will use the Replace option to replace text Yes with Greater than 2000 in the formula of the >2000 or not column. 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. After that, the Find and Replace dialog box will appear.
➤ Write and select the followings

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

➤ Select the Replace All option. Then, a message box will appear which says “All done. We made 9 replacements.” Result:
Afterward, you will be able to replace Yes with Greater than 2000 in the formula. Read More: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)

### Method-3: Using Go to Special Option to Replace Text in Excel Formula

You can replace text Yes with Greater than 2000 in the formula of the >2000 or not column by using the Go to Special option also. Steps:
➤ Go to Home Tab >> Editing Group >> Find & Select Dropdown >> Go To Special Option. Then, the Go To Special wizard will open up.
➤ Select the Formulas option and press OK. After that, the cells of the >2000 or not column will be selected. ➤ Follow Method-2 and you will get the new formula with text Greater than 2000 instead of Yes. ### Method-4: Using a Shortcut Key to Replace Text in Excel Formula

Here, we will use a shortcut key to replace text in the following formula easily. 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. Now, follow Method-2 and you will get the new formulas with a text Greater than 2000 instead of Yes. ➤ 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. ### 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. Step-01:
➤ Go to Developer Tab >> Visual Basic Option Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option After that, a Module will be created. 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,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

Result:
In this way, you will be able to replace 0.06 with 0.04 in the formulas of the Discounted Price column. ### 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. 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 ➤ Press ENTER.
➤ Drag down the Fill Handle tool. 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. To do this, we have to use a VBA code to create a function at first.

Step-02:
➤ Write the following code

``````Function EVAL(value As String)

Application.Volatile
EVAL = Evaluate(value)

End Function``````

VOLATILE recalculates whenever calculation occurs in any cells on the worksheet and this VBA code will create a function named EVAL. ➤ Type the created function name in cell F5.

`=EVAL(E5)`

EVAL will return us the value of the formula in cell E5. ➤ Press ENTER and drag down the Fill Handle tool. Result:
After that, you will be able to replace 0.06 with 0.04 in the formulas of the New Price column. ### 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. 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 ➤ Press ENTER.
➤ Drag down the Fill Handle tool. 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. 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. ➤ Press ENTER.
➤ Drag down the Fill Handle tool. Result:
Finally, you will be able to replace 0.06 with 0.04 in the formulas of the New Price column. ## 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. ## Conclusion

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

## Related Articles #### Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  