How to replace the Text of a Cell Based on a Condition in Excel – 5 Easy Methods

Method 1 – Replacing Text using the Find and Replace Tool in Excel

This is the sample dataset.

Step 1:

• Go to the Home tab.
• Click Editing.
• In Find & Select, choose Replace or press Ctrl+H.

Step 2:

• The Find and Replace dialog box will open .

Step 3:

• Use the question mark (?) to find and then replace a word.
• Here,  “App?e” was used to find “Apple” and replace it with “Al”.

Step 4:

• Click Replace All.

One replacement is made.

Step 5:

• Use “A*e” to find “Apple” and replace it with “Al”.
• This applies to any word starting with “A” and ending with “e”.

Step 6:

• Press Replace All.

6 replacements are made.  All words starting with “A” and ending in “e” are replaced. Case sensitivity is not checked.

Step 7:

• Enter “Apple” in Find what.
• Enter “Guava” in Replace with.

Step 8:

• Press Replace All.

One replacement is made.

Note:

Match Case: Tick this box for case-sensitive replacements.

Within: Provides the option on where to search and replace.

Search: Offers two options. Search by row and search by column.

Look in: Sets specific data, like formulas, values, etc.

Read More: How to Find and Replace Multiple Words from a List in Excel

Method 2. Using the REPLACE Function with a Condition in a Cell

Step 1:

• A new column: “Modification” was added to the dataset.

Step 2:

• Data in  B9 is wrong. Update the modified data in E9.
• Enter the formula below in E9:

Step 3:

• Press Enter.

Data is replaced by applying the REPLACE function in E9.

Read More: How to Replace Text in Excel Formula

Method 3 – Applying the SUBSTITUTE Function to Replace the Text of a Cell

Data in B9 was replaced.

Step 1:

• Go to E9.
• Enter the formula below:

Step 2:

• Press Enter.

The SUBSTITUTE function replaced the text in a cell.

Read More: How to Substitute Multiple Characters in Excel

Method 4 – Using the IF Function to Replace the Text of a Cell

Check all the data in column B.

Step 1:

• Enter the following formula in E5:

Step 2:

• Press Enter.

Step 3:

• Drag the Fill Handle across the cells you want to fill.

The IF function checks the condition. If the condition is fulfilled, the modified result is returned.

Method 5 – Using a VBA macro to Replace the Text of A Cell

Step 1:

• Go to the Developer tab.
• Click Record Macro.
• A new window, Record Macro, will be displayed.
• Name it Replace_data.

Step 2:

• Enter the following VBA code.

Sub Replace_data()
Dim present_data As String
Dim modified_data As String
present_data = Range("B5").Value
modified_data = Replace(present_data, "Apple", "Mango")
Range("B5").Offset(0, 0).Value = modified_data
End Sub

Step 3:

• Press F5 to run the code.

“Apple” is replaced with “Mango” after running the VBA code.

Download Practice Workbook

Download the practice workbook to exercise.

Related Articles

• How to Find and Replace Text Color in Excel
• How to Show Dash Instead of Zero in Excel
• How to Find and Replace Using Formula in Excel
• How to Find and Replace within Selection in Excel
• How to Find and Replace Values in Multiple Excel Files

<< Go Back to Find and Replace | Learn Excel