Excel Remove Characters From Right: 7 Simple Methods

When you are working on Excel there are often times where you need to remove characters from the right for creating a different value from the existing value.There are some easy ways through which you can accomplish this task without any  issue.

Different situations will require implementing different methods to remove characters from the right. The way to remove characters for a text value result is different than to remove characters for a numeric value result. There is a different  way to remove characters from right if there is a delimiter which separates the desired result and unwanted characters.

I have prepared this article by explaining 7 different methods through which you can remove characters from right  efficiently in Excel for different circumstances.

7 Different Methods To Remove Characters From Right

There are different ways through which you can remove characters from right. By using these 7 different methods, you can easily remove characters in different situations from the right side of a text string in Excel.

First Method: Using The LEFT Function

While working on Excel you can use the LEFT function to remove characters from the right side of a text string.

The Basic Formula for this method is =LEFT(text,LEN(text)-n) .

Here,

text: refers to the Cell from which you need to remove the characters from.

n: refers to the number of characters that you need to remove.

Step by Step

First, you need to select the cell where you are going to place your new found result.

Using The LEFT Function 1

Then you need to put the aforementioned formula in the Formula Bar with the specific cell from which you want to remove the characters as well as the number of characters that you need to remove. In our case, we used the cell A2 from which we want to remove the last 4 characters.  The final formula ended up being =LEFT(A2,LEN(A2)-4) .

Using The LEFT Function 2

After putting the Formula on the Function bar, press ENTER. You will get your desired result.

If you want to remove the same amount of characters from the whole column then you can use the Fill Handle to Automatically remove them. But if the characters that you need to remove are different then you will need to remove them separately using the formula.

Using The LEFT Function 3

Second Method: Using VALUE Function Alongside LEFT Function for Numeric Values

The previous method is most suited for textual values. If you are working with numbers and need to remove characters from right to get a numeric value then this method is necessary. It uses VALUE Function alongside LEFT function to achieve the required value.

The generic Formula for this method is =VALUE(LEFT(text,(LEN(text)-n))) .

Similar to previous formula,

text: refers to the Cell from which you need to remove the characters from.

n: refers to the number of characters that you need to remove.

Step by Step

It is quite similar to the previous method. First, you will need to select the cell where you are going to place your result.

Using VALUE Function Alongside LEFT Function 1

Then you will need to put the formula in the Formula Bar with the specific cell and number of characters that you need to remove. The Cell we are taking our data from is A2 and the number of characters we are going to remove is 7.

The final formula ended up being =VALUE(LEFT(A2,(LEN(A2)-7))) .

Using VALUE Function Alongside LEFT Function 2

After pressing ENTER, you will get your result.

In this worksheet, the number of characters that need to be removed are different in every cell of the column. For this reason, the formula needs to be used separately for every single cell.

Third Method: Using Flash Fill to Remove Characters

Flash Fill is a great tool to remove unwanted characters from your worksheet. This tool works by following your pattern. You will need to give them an example of which type of result you are expecting. If the AI does not understand from your first example give some more.

After recognizing your pattern,it will automatically remove characters from the rest of the column.

Step by Step

First open your worksheet and select the first cell where you want the result after removing the characters. Manually write down the value after removing the characters yourself to give it as an example for the AI to work with.

I have shortened Monday from the Input column to Mon and Manually wrote it in the Result column.

After that go to the Data tab and select Flash Fill. You can also use the Keyboard shortcut which is CTRL+E.

Using Flash Fill to Remove Characters 1

The AI will analyze the example to understand the pattern. After it understands the pattern, it will automatically fill the rest of the column. In our case, the AI recognised our pattern and shortened the text following the given example.

Using Flash Fill to Remove Characters 2

If the AI is unable to understand the pattern you may need to give more than one example.

Fourth Method: Using VBA to Remove Characters

For this method we are going to use VBA. VBA is an abbreviated form of Visual Basics for Applications which can be used for creating custom automatic data processing functions, creating custom charts etc.

We are going to create a custom macro function to automatically remove characters from the right using VBA.

Step by Step

First , you will need to go to the Developer Tab. if you can not find it, go to the More Options from the File tab.

Using VBA to Remove Characters 1

Go to the Options and select Customize Ribbons. There you can add Developer Tab.

Using VBA to Remove Characters 2

After getting the Developer tab go to Visual Basics and click it. The Keyboard shortcut is ALT+F11.

Using VBA to Remove Characters 3

Then a new window for Microsoft Visual Basics for Applications will pop up. Go to Insert and select Module.

Using VBA to Remove Characters 4

In the Module, you will need to write the code that will be required to remove the characters from the right. The code that I have prepared is RemoveCharactersFromRight, which you can easily apply to  remove characters. The Code is:

Function RemoveCharactersFromRight(str As String, cnt_chars As Long)

RemoveCharactersFromRight = Left(str, Len(str) – cnt_chars)

End Function

Using VBA to Remove Characters 5

Then save the code and return to the worksheet. In the worksheet, first select the cell where you want to place the result. Then you will need to put your customized formula in the Formula Bar.

The Formula will change based on your targeted cell from which you need to remove characters and the number of characters you will need to remove. In our case, the formula ended up being =RemoveCharactersFromRight(A2,4) .

Then press ENTER and you will get your result.

If the following cells also have a similar amount of characters that need to be removed, then you can use the Fill Handle to automatically remove them.

Using VBA to Remove Characters 6

Fifth Method: UsIng Text to Columns Wizard

When working on Excel you might often face a situation where you need to remove characters from a cell but need that information on the next column. If that is the case then you will need to use Text to Columns Wizard. It is quite easy to use and is very helpful for certain circumstances. For this method to work properly there needs to be some delimiters like space or dash  through which you can easily identify the space where you need to break the line.

In our case we have used a worksheet that contains the names of some people. We will be separating their first name and last name from one another using Text to Columns wizard.

Step by Step

First you need to select the column which contains the text that you need to separate from each other. We have selected the Input column.

UsIng Text to Columns Wizard 1

Then you will have to go to the Data tab and select the Text to Columns.

After the pop up shows up, select the file type that best suits you to separate the data from one another. In our case, we have used the Delimited option as it helps to separate the first name and last name quite easily. You can use the Fixed width option if it suits your data type. Then press Next.

UsIng Text to Columns Wizard 2

Then you will need to select the option of the delimiter that is separating the datas from one another. We have used the Space option and selected Next.

UsIng Text to Columns Wizard 3

Then you will be given an option to which type of value you want your separated text to have. We have used the Text option. It will show you a small preview screen. Closely analyze if you have achieved what you were looking for.

UsIng Text to Columns Wizard 4

After pressing Finish, you will get your desired result. In our case the names got separated and we managed to remove characters from the right side of the string. The first names and last names got separated.

Sixth Method: Using LEFT Function to Remove Characters from Right Based on Delimiters

Like the previous method, In this method you can easily remove characters from the right of any text string based if they have delimiters. Unlike the previous method, this method uses formulas to achieve the result.

The generic formula that you need to use is: =LEFT(TRIM(text),LEN(TRIM(text))-FIND(“d”,TRIM(text))) .

Here,

text: refers to the Cell from which you need to remove the characters from.

d: refers to the delimiter that is separating the data from the characters that needs to be removed.

Step by Step

First of all you will need to select the cell where you want to place  the result.

Then write the formula in the Formula Bar with the specific cell from which you are going to remove the characters and the delimiter that is separating the required data. In our case, we have taken cell A2 and the delimiter is Space. The final formula ended up being, =LEFT(TRIM(A2),LEN(TRIM(A2))-FIND(” “,TRIM(A2))) .

Using LEFT Function to Remove Characters from Right Based on Delimiters 1

After pressing ENTER, you will get your character removed from the right based on your selected Delimiter.

You can use the Fill Handle to use the same formula for the rest of the Column and remove the characters.

Using LEFT Function to Remove Characters from Right Based on Delimiters 2

Seventh Method: Using MID Function to Remove Characters from Both Side

Sometimes when you are working on Excel, there comes circumstances where you need to remove characters from both Left and Right sides of the text string. In this type of situation, removing characters from each side separately will be really hectic. You can use the MID function to remove characters from both sides at once.

The Generic Formula is: =MID(A2,ln+1, LEN(A2) -(ln+rn)) .

Here,

text: refers to the Cell from which you need to remove the characters from.

ln: refers to the number of characters that needs to be removed from the left.

rn: refers to the number of characters that needs to be removed from the right.

Step by Step

First select the cell in which you want to place the result. In our worksheet we have selected B2.

Put the formula in the Formula Bar by completing it with the required cell number as well as the number of characters that need to be removed from left and right. We have taken the data from Cell A2. we need to remove 4 characters from the left side and 7 characters from the right side.

Our Final applicable formula ended up as =MID(A2,4+1, LEN(A2) -(4+7)) .

Using MID Function to Remove Characters from Both Side 1

After Pressing ENTER, you will find your result in the selected cell. You will need to use this formula repeatedly if there is a different number of characters you need to remove from each side by adjusting the formula for each cell.

Using MID Function to Remove Characters from Both Side 2

Final Thoughts

Excel is one of the most diverse software where you can solve your problem with different methods. Each of the methods that I have shown in this article is suitable for different circumstances.

You will get a quicker solution for your problem if you try to remove characters based on the situation. For that reason, it is better to know a decent amount of methods for problem solving to be more efficient.

Leave a Comment