Excel Remove Characters From Right (5 ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Excel Remove Characters From Right: 5 ways

1. Using LEFT to Remove Characters from Right

2. VALUE with LEFT Function for Numeric Values

3. Remove Characters From Right using VBA

4. Remove the Right Character Using Flash Fill

5. Remove Characters From Either Side at Once

Practice Section

Conclusion

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.

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.

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.

Similar Readings

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.

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.

Similar Readings

Practice Section

I have attached two extra sheets to practice these ways.

Download Workbook to Practice

Remove Characters from Right.xlsm

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.

Excel Remove Characters From Right (5 ways)