Md. Ahaduzzaman
Every now and then we may find ourselves in a situation where we want to replace a text with some specific character or word. We sometimes need to update our data collection in companies, business records, school result records, etc. So often we may want to change some text in the records to bring the update. In this article, we are going to learn three easy and efficient ways in Excel to replace text after a specific character.
📁 Download Excel File
Download the practice file from here.
Learn to Replace Text After a Specific Character in Excel with These 3 Methods
Here we will discuss 3 ways in Excel to replace text after a specific character. Let’s assume we possess a dataset of some international employees of a company. But now we are trying to modify the full address record. We want to put “in” in front of the country names instead of the comma. For our demonstration purpose, the sample datasheet is as follows.
1. Applying Find & Replace Tool
In this section, we are going to learn how to use the FIND & REPLACE tool in Excel to replace text after a specific character. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select our data range.
- After that, we go to the Editing option.
- Following that we select the Find & Select option.
- Finally, we select the Replace option.
- We put a comma in the Find what box.
- We write “in” in the Replace with box.
- After that, we select Replace All option.
- We get a dialogue box with the confirmation.
- Finally, we get our result in column F.
📕 Read More: 3 Easy Ways to Find and Replace in Multiple Excel Files
2. Employing REPLACE Function
Here we will discuss how we can use the REPLACE function in Excel to replace text after a specific character. Therefore the breakdown steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the formula box of cell D6 and put this formula.
=REPLACE(C6,30,1,” in “)
🔨 Formula Breakdown
👉 Here C6 denotes our old text.
👉 30 is the character number from the left after which we want to make a change.
👉 1 means we want to replace only one character.
👉 ” in ” is our replaced text.
- We hit Enter and get the result in cell D6.
- Just like cell D6, we write this formula in cell D7.
=REPLACE(C7,20,1,” in “)
- We get the result in cell D7.
- We do a similar thing in the other two cells and get results.
📕 Read More: 7 Ways to Find and Replace Multiple Values in Excel
3. Applying SUBSTITUTE Function
In our final method here we will learn the application of the SUBSTITUTE function in Excel to replace text after a specific character. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- First, we go to the cell D6 formula box and type this formula.
=SUBSTITUTE(C6,”,”,” in “)
🔨 Formula Breakdown
👉 Here C6 is the text we want to update.
👉 “,” denotes the specific character in C6 that we want to change.
👉 ” in ” is our replaced text.
- We use the Fill Handle tool to drag the formula.
- Finally, we get our result in column D.
📕 Read More: How to Find and Replace Using Wildcards in Excel
How to Remove Text After Specific Character in Excel
In this section, we will learn how to remove texts after a certain character using the Find & Select option. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select the necessary data range.
- Following that, we go to the Editing option.
- After that, we select the Find & Select option.
- Finally, we select the Replace option.
- Firstly we put a comma with the asterisk symbol in the Find what box.
- Then we keep it blank in the Replace with box.
- After that, we select the Replace All option.
- We will see that text after the comma is removed.
📕 Read More: 5 Tricks to Insert Comma Between Words in Excel
How to Remove Text After Space in Excel
In this particular section, we will discuss how to remove the text after space in Excel. Therefore the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell D6 formula box and write this formula.
=LEFT(C6,FIND(” “,C6)-1)
🔨 Formula Breakdown
👉 Here FIND(” “,C6)-1 returns us a number of characters after the first space.
👉 LEFT(C6,FIND(” “,C6)-1) gives us the specific text from the left at the character number.
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we get the result in column D.
📄 Important Notes
🖊️ Whenever we use Excel functions we have to be very careful about the function argument.
🖊️ When we use the REPLACE function we have to put the start number of the character properly.
📝 Takeaways from This Article
📌 We can use the FIND & REPLACE tool in Excel to replace text after a specific character.
📌 REPLACE function is another way in Excel to replace text after a specific character.
📌 Also we can use the SUBSTITUTE function in Excel to replace text after a specific character.
Conclusion
In this article, we have learned three different ways in Excel to replace text after a specific character. We can use the FIND & REPLACE tool for this purpose. Apart from this, we can also use Excel functions like REPLACE, SUBSTITUTE, etc. Users can use whatever way they feel comfortable with. If you have any queries regarding this particular article you can simply put your comment in our comment box. The writer will try to come up with a solution. If you want to explore further excel related topics and their solutions please visit our website www.Excelden.com.
