Excel Remove Characters From Right (5 ways)

Using left Function to remove right characters

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.

Sample Datasheet

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)

Using left Function to remove right characters

⮚ Finally, press ENTER

The last character from the selected B4 cell will be removed.

Using left Function to remove right characters

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)

Using left Function to remove multiple characters from right

⮚ Finally, press ENTER

Here, the last 5 characters from the selected value of B4 will be removed.

Using left Function to remove multiple characters from right

Now, you can apply the Fill Handle to AutoFit the formula for the rest of the cells.

Using left Function to remove multiple characters from right

Read more: Remove Last Character from String Excel

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)))

Using VALUE Function to remove multiple characters from right

⮚ 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.

Using VALUE Function to remove multiple characters from right

Depending on how many string characters are there with the number character you will need to rewrite the formula.

Using VALUE Function to remove multiple characters from right

🔺 If all the number characters have the same string characters you can use Fill Handle.

Read more: How to Remove the Last 3 Characters in Excel

3. Remove Characters From Right using VBA

⮚ First, open Developer tab >> then select Visual Basic

⮚ You also can use ALT + F11

Using VBA

A new window of Microsoft Visual Basic for Applications will appear. Then open Insert tab >> then select Module.

Using VBA to remove character

Here the Module is opened.

Using VBA to remove character

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

Using VBA to remove character

⮚ 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)

Using VBA to remove character

⮚ Finally, press ENTER.

As I selected cell B4 the right characters of this cell will be removed.

Using VBA to remove character

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)

Using VBA to remove numeric character

⮚ Finally, press ENTER

As I selected cell C4 the right characters of this cell will be removed.

Using VBA to remove numeric character

Read more: VBA to Remove Characters from String in Excel

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.

Using Flash Fill command

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.

Using Flash Fill command

⮚ 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.

Using Flash Fill command

As I selected the Flash Fill the right characters of the rest of the cells will be removed.

Using Flash Fill command

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.

Using MID function

⮚ 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))

Using MID function

⮚ To apply the formula, press ENTER

In the meantime, from the selected cell both right and left characters will be removed except the Name.

Using MID function to remove both left and right character

For the rest of the cells depending on their characters number, I applied the MID function.

Using MID function to remove both left and right character

Practice Section

I have attached two extra sheets to practice these ways.

Practice shhet1

Practice sheet2

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.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo