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.

**Table of Contents**hide

## Download Workbook

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

**Read More:**** How to Find and Replace in Excel Column (6 Ways)**

__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**.

**Similar Readings**

**Excel VBA: How to Find and Replace Text in Word Document****How to Replace Text between Two Characters in Excel (3 Easy Ways)****How to Find and Replace within Selection in Excel (7 Methods)****How to Find and Replace from List with Macro in Excel (5 Examples)****How to Replace Text after Specific Character in Excel (3 Methods)**

__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.

**Read More: Excel VBA to Find and Replace Text in a Column (2 Examples)**

__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**:

➤ 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 calculation occurs in any cells on the worksheet and this **VBA **code will create a function named **EVAL**.

➤ After saving the code, return to the worksheet.

➤ 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.

**Read More:** **How to Use the Substitute Function in Excel VBA (3 Examples)**

__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.

**Read More:** **Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)**

## 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**

**Excel Formula to Replace Text with Number (5 Examples)****Find And Replace Values Using Wildcards in Excel****How to Find and Replace Asterisk (*) Character in Excel****Find and Replace Values in Multiple Excel Files (3 Methods)****Find And Replace Multiple Values in Excel (6 Quick Methods)****Excel VBA to Replace Blank Cells with Text (3 Examples)****Excel VBA: How to Replace Text in String (5 Effective Ways)**