Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss how to replace the text of a cell based on condition in Excel. Most people know how to substitute in Excel, but we will show some tricky methods to replace text in Excel based on condition.


Replace Text of a Cell Based on Condition in Excel: 5 Methods

In this article, we will discuss some methods to replace the text of a cell based on condition.

1. Replace Any Text by Find and Replace Tool in Excel

We will use the Find and Replace tool option to replace text from a cell in Excel. This tool can be avail from ribbon or mouse shortcuts.

We will apply this method to the data set below.

Step 1:

  • Go to the Home tab.
  • Click on Editing.
  • Then, pick Replace from the Find & Select tool.
    Or we can press the Ctrl+H Hotkeys too.

Replace Any Text by Find and Replace Tool in Excel

Step 2:

  • We will get a new dialog box named Find and Replace.

We can replace any text using this window. First, we need to find the text and then replace that text. We can use exact words or wildcards in the case of a partial match.

Step 3:

  • Now, we will use the question mark (?) in the word to find and then replace that word.
  • We used “App?e” to find “Apple” and replace it with “Al”.

Replace Any Text by Find and Replace Tool in Excel

Step 4:

  • Then click Replace All.

Replace Any Text by Find and Replace Tool in Excel

Here, we can see that one replacement is made, and the result is showing.

Now, we will apply another wildcard asterisk (*).

Step 5:

  • We apply “A*e” to find “Apple” and replace it with “Al”.
  • This will be any word starting with “A” and ending with “e”.

Replace Any Text by Find and Replace Tool in Excel

Step 6:

  • Again, press Replace All.

We see that 6 replacements were accomplished. It replaces any word that starts with “A” and ends with “e”. It does not check case sensitivity.

Now, we will apply the exact word to replace it from the data set.

Step 7:

  • Write “Apple” in the Find what box.
  • Then type “Guava” in Replace with.

Replace Any Text by Find and Replace Tool in Excel

Step 8:

  • Again, press Replace All.

One replacement happens here. This is how we use the Find & Replace tool in this section.

Note:

Replace Any Text by Find and Replace Tool in Excel

Match Case: When we need to ensure case-sensitive cases tick this box.

Within: This box gives an option where to search and replace a word. This may be a sheet or workbook.

Search: It offers two options. One is searched by row, and the other is searched by column.

Look in: It sets specific data types. Like formulas, values, etc.

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


2. Use REPLACE Function with a Condition in a Cell

The REPLACE function is used to supersede a certain number of letters from a word and form a new word in Excel. In this section, we will discuss how this REPLACE function works with the condition.

We assume that some of our data in the dataset is wrong. We will correct that data using the Replace function.

Step 1:

  • Here, we add a new column named “Modification” to the dataset.

Use REPLACE Function with a Condition in a Cell

Step 2:

  • We identified that the data of cell B9 is wrong. We will update the modified data on cell E9.
  • Write the formula given below in cell E9:
=REPLACE(B9,1,5,"Blue")

Use REPLACE Function with a Condition in a Cell

Step 3:

  • Then, press Enter.

We get the replaced data by applying the REPLACE function in cell E9.

Read More: How to Replace Text in Excel Formula


3. Apply SUBSTITUTE Function to Replace Text of a Cell

The SUBSTITUTE function replaces text from a text string with an instance number. Here, we will apply this function to replace the text of a cell with a condition.

We again will consider the condition mentioned in the previous method.

We will replace the data of cell B9.

Apply SUBSTITUTE Function to Replace Text of a Cell

Step 1:

  • Go to cell E9.
  • Apply the SUBSTITUTE function here. Write the formula below:
=SUBSTITUTE(B9,"Straw","Blue",1)

Apply SUBSTITUTE Function to Replace Text of a Cell

Step 2:

  • Then, press Enter.

This way, we apply the SUBSTITUTE function and replace text from a cell.

Read More: How to Substitute Multiple Characters in Excel


4. Use of IF Function to Replace Text of a Cell

In this section, we will apply conditions using the IF function. The IF function returns a result based on a condition.

Here, we will check all the data in column B.

Use of IF Function to Replace Text of a Cell

Step 1:

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

Use of IF Function to Replace Text of a Cell

Step 2:

  • Then, press Enter.

Step 3:

  • Drag the Fill Handle icon towards the last cell.

Use of IF Function to Replace Text of a Cell

Here, the IF function checks the condition. If the condition is fulfilled, it returns the modified result. Otherwise, data remains the same.


5. VBA macro to Replace Text from A Cell

We will apply a piece of VBA macros to replace a text from a cell in this section.

Step 1:

  • First, go to the Developer tab.
  • Click Record Macro.
  • A new window named Record Macro will appear.
  • Named this as Replace_data.

Step 2:

  • Apply the following VBA code on the command module.
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:

  • Then, press F5 to run the code.

VBA macro to Replace Text from A Cell

Here, we can see that the “Apple” data is modified to “Mango” after running the VBA code.


Download Practice Workbook

Download the following practice workbook to exercise while you are reading this article.


Conclusion

In this article, we showed how to replace the text of a cell based on a condition in Excel. I hope this will satisfy your needs. Please, comment below if you have any suggestions in the comment box.


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