Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Remove Texts Before or After a Specific Character From Cells in Excel?
Powerful spreadsheet programmes like Excel provide a wide range of features to alter and clean up data. Getting rid of text that comes before or after a specific character in a cell is a common data cleaning activity. In this article, we'll walk you through each step of how to complete this work using the built-in functions in Excel. Excel offers the tools you need to organise your data and save time, whether you need to get rid of prefixes, suffixes, or any other undesirable text.
Whether you're a novice or a seasoned Excel user, becoming proficient in text manipulation techniques will unquestionably improve your data management skills. So let's get started and discover how to quickly and efficiently clean up your data using Microsoft Excel!
Remove Texts Before or After a Specific Character From Cells
Here we will first get any one of the values using the formula, then use the autofill handle to complete the task. So let us see a simple process to know how you can remove text before or after a specific character from cells in Excel.
Step 1
Consider an Excel sheet where you gave a list of strings similar to the below image.
First, to remove the text before the character, click on an empty cell and enter the formula as
=RIGHT(A2,LEN(A2)-FIND(" ",SUBSTITUTE(A2,"r"," ",(LEN(A2)-LEN(SUBSTITUTE(A2,"r","")))/LEN(" ")))) and click enter to get the first value. Then drag down using the autofill handle.
Empty Cell > Formula > Enter > Drag.
Step 2
Now to remove the text after the specific character, click on an empty cell and enter the formula as =LEFT(B5,FIND(" ",SUBSTITUTE(B5,"r"," ",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1) and click enter. Then drag down using the autofill handle.
Empty Cell > Formula > Enter > Drag.
This is how you can remove text before or after specific characters in Excel.
Conclusion
In this tutorial, we have used a simple process to learn how you can remove text before or after a specific character from cells in Excel to highlight a particular set of data.
216 Views
