Alok
Microsoft Excel is one of the most popular applications for corporate service holders. It lessens the obstacles of working with old papers of previous years. 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.
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
5 Methods to Replace Text of a Cell Based on Condition in Excel
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 below data set.
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 use 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 search by row, the other is search by column.
Look in: It sets specific data types. Like formulas, values, etc.
Read more: How to Find And Replace Values Using Wildcards 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 with condition.
We assume that some of our data in the dataset are wrong. We will correct that data using the Replace function.
Step 1:
- Here, we add a new column named Modification in 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:
=REPLACE(B9,1,5,"Blue")
Step 3:
- Then, press Enter.
We get the replaced data applying the REPLACE function in Cell E9.
Read More: How to Find and Replace Using Formula in Excel (4 Examples)
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:
=SUBSTITUTE(B9,"Straw","Blue",1)
Step 2:
- Then, press Enter.
This way, we apply the SUBSTITUTE function and replace text from a cell.
Read More: How to Replace Text in Selected Cells in Excel (4 Simple Methods)
Related Readings
- Excel VBA to Replace Blank Cells with Text (3 Examples)
- How to Replace Special Characters in Excel (6 Ways)
- Excel VBA: How to Replace Text in String (5 Effective Ways)
- Find And Replace Multiple Values in Excel (6 Quick Methods)
- How to Find and Replace Multiple Words from a List 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 of column B.
Step 1:
- Apply the following formula in Cell E5:
=IF(B5="Strawberry", "Blueberry", B5)
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.
Read More: How to Replace Text with Blank Cell in Excel (5 Simple Methods)
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.
Read More: Excel VBA to Find and Replace Text in a Column (2 Examples)
Conclusion
In this article, we showed how to replace a text of a cell based on a condition in Excel. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.
Further Readings
- How to Replace Text in Excel Formula (7 Easy Ways)
- [Fixed!] Excel Find and Replace Not Working (6 Solutions)
- How to Use the Substitute Function in Excel VBA (3 Examples)
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)
- Substitute Multiple Characters in Excel (6 Ways)
- How to Find and Replace Asterisk (*) Character in Excel
