This article is about how to remove text in Excel. In this Excel tutorial, you will learn
-To remove part of texts
-To remove texts before, after, and in between specific parameters
-Different formulas that can remove texts based on different circumstances.
We have used Microsoft 365 while preparing this article. But all the methods discussed here are also applicable to all the other versions of Excel.
Removing texts is important for data cleaning, reduction, and extraction. Raw data are always not necessary in the actual usage. So removing unnecessary ones are essential for final datasets and actual applications.
Download Practice Workbook
How to Remove Text in Excel?
There are primarily three major ways to remove text in Excel. We can delete them using Flash Fill and Find and Replace features, and using formulas.
1. Remove Part of Text with Flash Fill Feature
Flash Fill is a feature available in Microsoft Excel that automatically fills in values based on patterns. You can give hints to Excel by writing some of the deleted texts first. Then the Flash Fill feature will automatically pick up and remove text for the rest of the cells in the pattern.
- Type the first portion of Full Name in the cell next to it and press Enter.
- Now, write a few letters in the next cell, and it will automatically show all the first names removing the last names. And press Enter.
2. Delete Text with Find and Replace Option
We can also easily remove text using the Find and Replace option in Excel. This feature will remove whatever is in the Find what field and replace them with what you put it in the Replace with field. If you have nothing in the Replace with field, the feature will just remove the value we put in the former field.
We are going to use this method in three different scenarios for the demonstration.
2.1. Delete Text Before Comma
- Press Ctrl+H. Then, the Find and Replace dialogue box will appear.
- There, in the Find What box, type
*,- Click on Replace All.
- Now, press OK.
- Text before comma will be removed as follows.
2.2. Delete Text After Comma
- First of all, press Ctrl+H. Then, the Find and Replace dialogue box will appear.
- In the Find What box, type
,*- Click on Replace All.
- Now, click on OK.
- Text after comma will be removed as shown in the picture below.
2.3. Remove Text in Between Commas
In this example, we will be removing text in between commas,
- The find and Replace dialogue box will appear after pressing Ctrl+H.
- In the Find What box, type
,*,- Click on Replace All.
- Now, press OK.
- Text surrounded by commas will be removed from the cell as the dataset below.
Read More: Remove Text between Two Characters in Excel
3. Use Formulas to Remove Text
Different functions like SUBSTITUTE, REPLACE, TRIM, etc. offer ways to delete text directly. Combine them with functions like SEARCH, FIND, LEFT, RIGHT, etc. and you will get usable formulas to remove text based on particular conditions.
3.1. Remove Text After or Before a Character
To remove text after commas, we will use the combination of LEFT and SEARCH functions here.
- Select cell C5 and write the formula.
=LEFT(B5, SEARCH(",", B5) -1)- Press Enter.
How the Formula Works:
- SEARCH(“,”, B5): This function finds where the comma (“,”) is located in the text inside cell B5.
- LEFT(B5, SEARCH(“,”, B5) – 1): This uses the LEFT function to take the text from cell B5 but only up to the character just before the comma, determined by the result of the SEARCH
The RIGHT, LEN, and SEARCH function combinations are used in the next example to remove text before commas.
- In cell C5, press Enter after inserting the formula below
=TRIM(RIGHT(B5, LEN(B5) - SEARCH(",", B5)))
How the Formula Works:
- SEARCH(“,”, B5): This part finds where the comma is located in the text inside cell B5.
- LEN(B5) – SEARCH(“,”, B5): Here, we calculate the difference between the total length of the text in B5 and the position of the comma. This gives us the number of characters to consider from the comma onwards.
- RIGHT(B5, LEN(B5) – SEARCH(“,”, B5)): The RIGHT function then extracts the characters starting from the position after the comma.
- TRIM(RIGHT(…)): The TRIM function removes any extra spaces that might be at the beginning of the extracted text.
3.2. Delete Text After or Before Nth Occurrence of Character
Here we will remove the text after the first comma with the help of the LEFT, FIND, and SUBSTITUTE functions.
Generic Formula:
LEFT(cell, FIND(“#”, SUBSTITUTE(cell, “char”, “#”, N)) -1)
Where “cell” is the cell that contains data, “N” represents the order of occurrence. To use the formula, these two parameters are to be set.
- Write the formula in cell C5, and press Enter.
=LEFT(B5, FIND("#", SUBSTITUTE(B5, ",", "#", 1)) -1)
Note: Wondering why # is there? The formula requires a symbol that is not present in the dataset. If the dataset contains # already, then make sure to use other symbols.
How the Formula Works:
- SUBSTITUTE(B5, “,”, “#”, 1): This part replaces the first comma in the text inside cell B5 with a hash symbol “#”. This helps identify the first comma’s position without affecting the original text.
- FIND(“#”, SUBSTITUTE(B5, “,”, “#”, 1)): Here, we find the position of the hash symbol “#”, which corresponds to the position of the first comma in the modified text.
- FIND(“#”, SUBSTITUTE(B5, “,”, “#”, 1)) – 1: We subtract 1 from the position of the hash symbol to get the position just before the comma.
- LEFT(B5, FIND(“#”, SUBSTITUTE(B5, “,”, “#”, 1)) – 1): The LEFT function then takes the text in B5 and extracts the characters up to the position just before the comma.
In this example, we will remove all the text before the second comma. To do so, follow the steps ahead.
Generic Formula:
Make sure to change “cell” and “N” according to your needs.
- Use the formula in cell C5 and make sure to press Enter.
=TRIM(RIGHT(SUBSTITUTE(B5, ",", "#", 2), LEN(B5) - FIND("#", SUBSTITUTE(B5, ",", "#", 2))))
3.3. Remove Text After or Before Last Occurrence of Character
Here, we will remove the text after the last comma in each cell of the dataset.
Generic Formula:
- In cell C5, write the following formula and press Enter.
=LEFT(B5, FIND("#", SUBSTITUTE(B5, ",", "#", LEN(B5) - LEN(SUBSTITUTE(B5, ",","")))) -1)
To remove the text before the last comma, follow the steps.
- Utilize the formula in Cell C5, and press Enter.
=TRIM(RIGHT(B5, LEN(B5) - FIND("#", SUBSTITUTE(B5, ",", "#", LEN(B5) - LEN(SUBSTITUTE(B5, ",",""))))))
Frequently Asked Questions
1. How do I trim unwanted text in Excel?
To trim unwanted text in Excel, you can use the following methods:
- TRIM function: Removes extra spaces from the beginning and end of a text string. Example: =TRIM(A1)
- LEFT, RIGHT, and LEN functions: Remove a specific number of characters from the start or end of a text string.
Example: To remove the first 3 characters from A1: =RIGHT (A1, LEN(A1)-3) To remove the last 3 characters from A1: =LEFT (A1, LEN(A1)-3)
- SUBSTITUTE function: Removes specific characters or strings from a text string. Example: To remove all “#” characters from A1: =SUBSTITUTE (A1, “#”, “”)
2. How do I remove 5 characters from right in Excel?
To remove 5 characters from the right side of a text string in Excel, use this formula:
=LEFT (A1, LEN(A1)-5)Replace “A1” with the cell reference containing your text string. The formula will extract the characters from the left side, excluding the last 5 characters.
3. How do I remove the first 3 characters in Excel?
To remove the first 3 characters from a text string in Excel, you can use the RIGHT function along with the LEN function. Here’s a simple formula:
=RIGHT (A1, LEN(A1) - 3)Replace “A1” with the cell reference containing your text string. The formula will extract the characters from the right side, excluding the first 3 characters.
Conclusion
This article mainly deals with all the possible ways of removing Texts, and Letters in a Cell. Hope all the methods in the article help you to clear your basic understanding of removing texts and letters in datasets.
If you have any problem regarding this article, you can comment on your problem below. Our team will get back to you as soon as possible. Thanks for considering us. You can also recommend us to your friends and colleagues if they are looking for a complete platform for building skills in Excel.
Excel Remove Text: Knowledge Hub
- Remove Specific Text from Cell in Excel
- Remove Letters from Cell in Excel
- Remove Text from an Excel Cell but Leave Numbers
- Remove Text After Character in Excel
- Remove Everything After a Character in Excel
- Remove Text before a Space with Excel Formula
<< Go Back To Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
