We’ll use the following simple dataset to showcase how you can remove text.
Download the Practice Workbook
How to Remove Text in Excel
Method 1 – Remove Parts of Text with the Flash Fill Feature
- Type in the first portion of the Full Name in the cell next to it and press Enter.
- Type a few letters in the next cell, and Excel will automatically show all the first names removing the last names.
- Press Enter.
Method 2 – Delete Text with the Find and Replace Option
Case 2.1 – Delete Text Before Comma
- Press Ctrl + H. The Find and Replace dialog box will appear.
- In the Find What box, type
*,- Click on Replace All.
- Press OK.
- The text before commas will be removed as follows.
Case 2.2 – Delete Text After Comma
- Press Ctrl + H. The Find and Replace dialog box will appear.
- In the Find What box, type
,*- Click on Replace All.
- Click on OK.
- Text after the commas will be removed as shown in the picture below.
Case 2.3 – Remove Text in Between Commas
- Press Ctrl + H.
- In the Find What box, type
,*,- Click on Replace All.
- Press OK.
- Text surrounded by commas (along with the commas) will be removed from the cell as the dataset below.
Read More: Remove Text between Two Characters in Excel
Method 3 – Use Formulas to Remove Text
Case 3.1 – Remove Text After or Before a Character
To remove text after commas:
- Select cell C5 and use this 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
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.
Case 3.2 – Delete Text After or Before the N-th Occurrence of Character
Generic Formula for Removing Text After N Certain Characters:
LEFT(cell, FIND(“symbol”, SUBSTITUTE(cell, “char”, “symbol”, N)) -1)
Where “cell” is the cell that contains data, “N” represents the order of occurrence, and symbol is another helper symbol.
- Insert this formula in cell C5 and press Enter.
=LEFT(B5, FIND("#", SUBSTITUTE(B5, ",", "#", 1)) -1)Note: The formula requires a symbol that is not present in the dataset. If the dataset contains # already, use another symbol.
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 for Removing Text Before N Certain Characters:
- Use this formula in cell C5 and press Enter.
=TRIM(RIGHT(SUBSTITUTE(B5, ",", "#", 2), LEN(B5) - FIND("#", SUBSTITUTE(B5, ",", "#", 2))))
Case 3.3 – Remove Text After or Before Last Occurrence of Character
Generic Formula for Removing Text After the Last Occurrence of a Character:
- In cell C5, use 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, use this formula instead:
=TRIM(RIGHT(B5, LEN(B5) - FIND("#", SUBSTITUTE(B5, ",", "#", LEN(B5) - LEN(SUBSTITUTE(B5, ",",""))))))
Frequently Asked Questions
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. 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. To remove all “#” characters from A1: =SUBSTITUTE (A1, “#”, “”)
How do I remove 5 characters from the 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.
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.
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!
