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 Specified Number of Characters from the Right 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 All Characters from the Right After a Specific Character 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 the Right in Excel Using Find and Replace
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.
- 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 the Right in Excel Using Flash Fill
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.
- 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 the Right in Excel with 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 Function
Syntax
=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.”
Can you provide an example of using the “LEFT” function to remove characters from the right?
Certainly! If you have a text string in cell A1, and you want to remove the last 3 characters, you can use the formula =LEFT(A1, LEN(A1)-3).
Can I remove a specific number of characters regardless of their position from the right?
Yes, you can. If you want to remove, for instance, the last 5 characters from any position in a text string in cell A1, you can use the formula =LEFT(A1, LEN(A1)-5).
Related Articles