Shamima Sultana
It is possible to have any extra characters in the Excel cell. Sometimes, to generate a different value from existing values you may require to remove characters. In this article, I’m going to explain 5 ways of Excel remove characters from right.
To make it clearer, I’m going to use a datasheet of order information of some clients which has 4 columns.
This table represents the order information of different users. The columns are Name with ID, Order, Name, and Order Quantity.
Download Workbook to Practice
5 Ways to Remove Characters From Right
1. Using LEFT to Remove Characters from Right
To remove the only last character you can use the LEFT function.
⮚ First, select the cell where you want to place your new value after removing the last character.
⮚ Then type the formula in the Formula Bar. I selected the B4 cell. Here I want to show the Name only so I will remove the number strings from the right.
The formula is
=LEFT(B4,LEN(B4)-1)
⮚ Finally, press ENTER
The last character from the selected B4 cell will be removed.
Here only removing a single character doesn’t match the context of our example, so let’s remove multiple characters.
⮚ First, select the cell where you want to place your new value after removing the multiple characters from the right.
⮚ Then type the formula for the B4 cell from here I want to remove multiple characters. I  want to remove 5 characters from the right.
The formula is
=LEFT(B4,LEN(B4)-5)
⮚ Finally, press ENTER
Here, the last 5 characters from the selected value of B4 will be removed.
Now, you can apply the Fill Handle to AutoFit the formula for the rest of the cells.
Read More: How to Remove Characters from Left in Excel (6 Ways)
2. VALUE with LEFT Function for Numeric Values
While dealing with numeric values, to remove characters from the right you can use the LEFT function and the VALUE function together.
⮚ First, select the cell where you want to place your new value after removing the characters from the right.
⮚ I selected the B4 cell then typed the formula. Here I want to remove characters from the right and will keep only the Order Quantity. So, I will remove all the string characters from the right except the number.
The formula is
=VALUE(LEFT(C4,(LEN(C4)-8)))
⮚ Finally, press ENTER
The string characters of the C4 cell will be removed from the right. You will only see the numeric values in Number format in the Order Quantity column.
Depending on how many string characters are there with the number character you will need to rewrite the formula.
🔺 If all the number characters have the same string characters you can use Fill Handle.
Read More: How to Remove Non-numeric Characters from Cells in Excel
3. Remove Characters From Right using VBA
⮚ First, open Developer tab >> then select Visual Basic
⮚ You also can use ALT + F11
A new window of Microsoft Visual Basic for Applications will appear. Then open Insert tab >> then select Module.
Here the Module is opened.
In a moment, write the code to RemoveRightCharacter in the module.
Function RemoveRightCharacter(str As String, cnt_chars As Long)
RemoveRightCharacter = Left(str, Len(str) - cnt_chars)
End Function
⮚ After that, Save the code and go back to the worksheet.
⮚ First, select a cell where you want to keep your new value after removing the character from the right.
⮚ Then type the formula for the B4 cell. Type the function name that you have written in the module.
⮚ As my function name is RemoveRightCharacter it will show this name.
The formula is
=RemoveRightCharacter(B4,5)
⮚ Finally, press ENTER.
As I selected cell B4 the right characters of this cell will be removed.
You also can use it to show the number character.
⮚ First, select a cell where you want to keep your new value after removing the character from the right.
⮚Then type the formula for the C4 cell. Type the function name that you have written in the module. Now I want to show the Order Quantity. As my function name is RemoveRightCharacter it will show this name.
The formula is
=RemoveRightCharacter(C4,8)
⮚ Finally, press ENTER
As I selected cell C4 the right characters of this cell will be removed.
Read More: How to Remove Characters from String Using VBA in Excel
Similar Readings
- How to Remove Apostrophe in Excel (5 Easy Methods)
- Remove Parentheses in Excel (4 Easy Ways)
- How to Remove Dollar Sign in Excel (7 Easy Ways)
- Remove Dashes in Excel (3 Methods)
- How to Remove Blank Characters in Excel (5 Easy Ways)
4. Remove the Right Character Using Flash Fill
You can use the Flash Fill command from the ribbon to remove the right character.
⮚ First, create a pattern example to use Flash Fill.
⮚ I provided the first example Steve by removing the right number of characters.
⮚ After that, select the example value open Data tab >> then select Flash Fill.
The keyboard shortcut to use Flash Fill  is CTRL + E
As I selected the Flash Fill the right characters of the rest of the cells will be removed.
⮚ If you want you can keep the number character by removing the string characters from the right.
⮚ Here, I provided the first example 32 where I only kept the number character by removing the right string characters. It created a pattern for Flash Fill.
⮚ After that, select the example value open Data tab >> then select Flash Fill.
As I selected the Flash Fill the right characters of the rest of the cells will be removed.
Read More: How to Remove Last Character from String Using VBA in Excel
5. Remove Characters From Either Side at Once
If you have a datasheet where multiple information is compressed together then the MID Function is suitable to extract the required information or data.
To show you how this function work I made an adjustment to the datasheet.
⮚ First, select a cell where you want to keep your new value after removing the character from both rights and left.
⮚ Then type the formula in the cell or in the formula bar. I selected the B4 cell. From that cell, I want the Name so I will remove all the right and left characters except the name Steve.
⮚ The formula is
=MID(B4, 11+1, LEN(B4) - (10+6))
⮚ To apply the formula, press ENTER
In the meantime, from the selected cell both right and left characters will be removed except the Name.
For the rest of the cells depending on their characters number, I applied the MID function.
Read More: How to Remove Characters from Left and Right in Excel
Practice Section
I have attached two extra sheets to practice these ways.
Conclusion
In this article, I explained 5 ways to remove characters from right in Excel. I hope these different approaches will help you to remove characters from right in Excel. For any kind of suggestions, ideas, and feedback you are most welcome. Feel free to comment down below.
Related Articles
- How to Remove Specific Character from String in Excel
- Remove Single Quotes in Excel (6 Ways)
- How to Remove First 3 Characters in Excel (4 Suitable Methods)
- Remove Hidden Double Quotes in Excel (6 Easy Ways)
- How to Remove Non-Alphanumeric Characters in Excel (2 Methods)
- How to Remove Semicolon in Excel (4 Methods)
