5 Methods to Remove Characters from the Right in Excel
We can remove characters from the right in Excel using either formulas, commands, or VBA scripts. I have discussed 5 approaches in total to remove characters from the right in Excel. Hope you will find your desired solution after reading this article.
Remove Characters from Right with LEFT and LEN Function in Excel
If you already know how many characters you want to remove from the right, you can use the LEFT and LEN functions to do that.
Syntax
=LEFT(text,LEN(text)-num_chars)Formula
=LEFT(A2,LEN(A2)-B2)Formula Explanation
- A2 has the text (Andre Botha) from where we want to remove characters.
- B2 has the number of characters (5) to remove from cell A2. You can also directly specify the number in the formula.
- LEN(A2) determines the total number of characters in cell A2 which is 11.
- The above formula ultimately becomes LEFT(Andre Botha,11-5) e. LEFT(Andre Botha,6).
- The LEFT function returns the first 6 characters from the left. Thus, the 5 characters are removed from the right.
To remove a specified number of characters from the right in Excel:
- Select cell C2.
- Insert the formula: =LEFT(A2,LEN(A2)-B2). Here, cell B2 specifies the number of characters to remove.
- Press ENTER.
- Select the entire column.
- Press CTRL + D. This will copy down the formula applied in cell C2 with changing cell reference.
Now see all the specified characters are removed from the right.
- 6 Ways to Remove the First 4 Characters in Excel
- Remove Non-Printable Characters in Excel [5+ Methods]
- 7+ Methods to Remove Characters from Left in Excel
Remove Characters from Right LEFT and SEARCH Functions in Excel
The formula that I used in this method can remove all characters from the right after a specific character.
Here, I will use the formula to delete everything after the first space encountered.
Syntax
=LEFT(text,SEARCH(find_text,within_text)-1)Formula
=LEFT(A2, SEARCH(" ", A2) -1)Formula Explanation
- A2 has the text (Andre Botha) from where we want to remove characters.
- SEARCH(” “, A2) returns the location of the first space (“”) encountered which is
- The LEFT function returns only the first 5 (6-1) characters from the left. So, everything after the first space encountered is deleted automatically.
Follow these steps to remove all characters from the right after a specific character in Excel:
- Select cell B2.
- Insert the formula: =LEFT(A2, SEARCH(” “, A2) -1)
- Press ENTER.
- Select the entire column.
- Press CTRL + D. This will copy down the formula applied in cell C2 with changing cell reference.
This formula removes everything after the first space from the right side of a string.
Remove Characters from Right in Excel Using Find and Replace Tool
Here, I will use the Find and Replace dialog box to remove all the characters from the right after a specific character.
In this case, the specific character is a space (“”). So, only the first name will be kept removing the last name.
Steps to remove characters from the right in Excel using the Find and Replace tool:
- Select all the texts.
- Press CTRL + H to open the Find and Replace dialog box.
- Click on the Find what box.
- Press the Space bar once to insert a space there.
- Then insert an asterisk (*) after the space and leave the Replace with box blank.
- Hit the Replace All button. This command will replace all the characters after the first space with null values.
After hitting the Replace All button, MS Excel will display a dialog box that says all replacement has been done. - Click OK in the small pop-up dialog box.
- Hit the Close button of the Find and Replace dialog box.
All the characters after the first space are replaced with null values. Thus, all the characters are removed from the right.
Clear Characters from Right in Excel Using Flash Fill Command
If your data has a pattern, then you can use the Flash Fill command to remove characters from the right.
To clear characters from the right in Excel using Flash Fill:
- Create a blank column right next to the column having the texts to remove.
- Type the texts without the characters from the right in the top two cells of the adjacent column.
- Select the whole column.
- Click on the Fill drop-down in the Home tab and click on Flash Fill.
Excel will automatically recognize the pattern and remove all the characters from the right after the first space.
- 3 Ways to Remove Specific Characters in Excel
- 5 Ways to Remove Blank Characters in Excel
- 5 Ways to Remove Numeric Characters from Cells in Excel
Remove Characters from Right in Excel with a VBA Code
The following code creates a function named Clear_Char_from_Right. You can use this customized function to remove characters from the right in Excel.
Option Explicit
Function Clear_Char_from_Right(pStr As String, pChars As Long)
Clear_Char_from_Right = Left(pStr, Len(pStr) - pChars)
End FunctionSyntax
=Clear_Char_from_Right(text, number_of_characters)Formula
=Clear_Char_from_Right(A2,B2)Formula Explanation
- A2 has the text from where we want to remove the characters from the right.
- B2 has the number of characters to remove from the right.
Now follow these steps to remove characters from the right using the custom formula:
- Select cell C2.
- Insert this formula: =Clear_Char_from_Right(A2,B2)
- Press ENTER.
- Select the entire column.
- Press CTRL + D. This will copy down the formula applied in cell C2 with changing cell reference.
All the specified characters are removed from the right.
Conclusion
I have discussed 5 different methods for removing characters from the right. I hope you’ve found your required solution from this blog.
Frequently Asked Questions
How can I remove characters from the right in Excel?
You can remove characters from the right in Excel using the Flash Fill tool or a combination of functions like “LEN” and “LEFT.”
How do I remove unwanted characters in Excel?
To remove unwanted characters in Excel, you can use the SUBSTITUTE function in combination with other functions like CLEAN or a custom formula. Here’s a general formula: =SUBSTITUTE(SUBSTITUTE(cell_reference, CHAR(160), “”), CHAR(10), “”)
Replace “cell_reference” with the reference to the cell containing the text. This formula removes unwanted characters, such as non-breaking spaces (CHAR 160) and line feeds (CHAR 10), from the specified cell in Excel. Customize the formula based on the specific unwanted characters you want to remove. Adjust the cell reference as needed for your case.
How do I remove a character from a string?
To remove a specific character from a string in Excel, you can use the SUBSTITUTE function. Here’s a concise formula: =SUBSTITUTE(cell_reference, “character_to_remove”, “”)
Replace “cell_reference” with the reference to the cell containing the text, and “character_to_remove” with the specific character you want to remove. This formula effectively replaces the specified character with an empty string, effectively removing it from the text. Adjust the cell reference and character as needed for your specific case.
How do I remove part of text in Excel?
To remove part of text in Excel, use the SUBSTITUTE function along with other relevant functions. For example, to remove a specific substring, use a formula like: =SUBSTITUTE(cell_reference, “text_to_remove”, “”)
Replace “cell_reference” with the reference to the cell containing the text, and “text_to_remove” with the specific substring you want to eliminate. This formula substitutes the specified substring with an empty string, effectively removing it from the original text. Adjust the cell reference and substring as needed for your specific case.
Related Articles
