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

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.

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

Step 4:

• Then click Replace All.

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

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.

Step 8:

• Again, press Replace All.

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

Note:

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.

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.

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:

Step 3:

• Then, press Enter.

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

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.

Step 1:

• Go to cell E9.
• Apply the SUBSTITUTE function here. Write the formula below:

Step 2:

• Then, press Enter.

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

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.

Step 1:

• Apply the following formula in cell E5:

Step 2:

• Then, press Enter.

Step 3:

• Drag the Fill Handle icon towards the last 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

Step 3:

• Then, press F5 to run the code.

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.

Further Readings

• How to Replace Special Characters in Excel 
• How to Replace Text in Excel Formula
• Substitute Multiple Characters in Excel
• How to Show Dash Instead of Zero in Excel