How to replace the Text of a Cell Based on a Condition in Excel – 5 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.

Replace Any Text by Find and Replace Tool in Excel

Step 2:

The Find and Replace dialog box will open .

Step 3:

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

Replace Any Text by Find and Replace Tool in Excel

Step 4:

  • Click Replace All.

Replace Any Text by Find and Replace Tool in Excel

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

Replace Any Text by Find and Replace Tool in Excel

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.

Replace Any Text by Find and Replace Tool in Excel

Step 8:

  • Press Replace All.

One replacement is made.

Note:

Replace Any Text by Find and Replace Tool in Excel

Match Case: Check 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.

Use REPLACE Function with a Condition in a Cell

Step 2:

Data in B9 is wrong. Update the modified data in E9.

  • Enter the formula below in E9:
=REPLACE(B9,1,5,"Blue")

Use REPLACE Function with a Condition in a Cell

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.

Apply SUBSTITUTE Function to Replace Text of a Cell

Step 1:

  • Go to E9.
  • Enter the formula below:
=SUBSTITUTE(B9,"Straw","Blue",1)

Apply SUBSTITUTE Function to Replace Text of a Cell

Step 2:

  • Press Enter.

The SUBSTITUTE function replaced the text.

Read More: How to Substitute Multiple Characters in Excel


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

  • Select data in column B.

Use of IF Function to Replace Text of a Cell

Step 1:

  • Enter the following formula in E5:
=IF(B5="Strawberry", "Blueberry", B5)

Use of IF Function to Replace Text of a Cell

Step 2:

  • Press Enter.

Step 3:

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

Use of IF Function to Replace Text of a Cell

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

VBA macro to Replace Text from A Cell

Step 3:

  • Press F5 to run the code.

VBA macro to Replace Text from A Cell

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


Download Practice Workbook

Download the practice workbook to exercise.


Related Articles


<< Go Back to Find and Replace | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo