How to Trim Right Characters and Spaces in Excel (5 Ways)

Sometimes our data cells may have some additional characters attached to the right which is not required. You can trim these characters from the right of your data cell. In this article, I’ll show five methods to trim the right characters in Excel.

How to Trim Right Characters: 5 Easy Ways

Let’s say we have a dataset where the Distance covered by different participants at a marathon is given. There are some spaces at the end of each Participant Name and the cells in the Distance Covered column have the numerical values as well as the unit- miles. Now we will trim the spaces and the characters which indicate the unit from the right.

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.

1. Utilizing TRIM Function to Remove Extra Spaces

Sometimes your data cells may have extra spaces at the right end. We can use the TRIM function to remove these spaces. As we mentioned, there are some spaces at the end of each Participant‘s Name.

To remove these spaces, type the following formula in an empty cell (D5)

=TRIM(B5)

Here, the TRIM function will remove the spaces from the right end.
Eventually, press ENTER to remove all the extra spaces and drag it down for other cells.

Read More: How to Trim Spaces in Excel

2. Employing Text to Columns Feature to Trim Right Spaces

You can also use Text to Columns features to trim the right spaces. This method will require a spare column at the right of the column from where you will remove the spaces. First, insert a column right to the column from where you will remove the space.

Firstly, go to Data>> select Text to Columns under the Data Tools group.

Apparently, a Text to Column Wizard window will appear. Select Fixed Width and click on Next.

In the second step, move the vertical line beside your data to the end of your data and select Next.

Consequently, you will see, all of your data is highlighted in black color. Finally, select Finish.

After that, a confirmation window will appear. Press OK.

Now you will see, there are no spaces to the right end of your dataset.

3. Using LEFT and LEN Functions to Trim Right Characters

With a combination of the LEFT function and the LEN function, you can easily trim the right characters from your data cells.

Firstly, type the following formula in an empty cell (D5).

=LEFT(C5,LEN(C5)-6)

Here, the LEFT function indicates that the formula will return the characters of the selected cell, C5 from the LEFT, and LEN(C5)-6 portion indicates that the last 6 characters from the total length of cell C5 will be exempted in the return of the LEFT function.

Eventually, press ENTER and you can see the right characters which are miles for our dataset have been removed.
After that, drag it down for getting the same formula for other cells.

4. Applying VALUE, LEFT, and LEN Functions

By the previous method, we get texts as a return in the output cell. If you want to get the numeric values, you have to use the VALUE function in your formula. To get the numeric value after trimming the right characters, type the following formula in cell D5,

=VALUE(LEFT(C5,LEN(C5)-6))

Here, the VALUE function will convert the return of the LEFT function into numeric values.

Subsequently, press ENTER. You will see the formula has trimmed the right characters. It will also show the return on the right side of the output cell, D5 which indicates the return is a numeric value.
Consequently, drag the D5 cell to apply the same formula in all other cells. As a result, you will get the distance covered without the unit miles in numeric formats at the output cells for all of the participants.

5. Trim Right Characters Incorporating VBA Macros

Another way to trim the right characters in Excel is by making a custom function using Visual Basic for Applications (VBA).

Firstly, navigate to the Developer tab >> choose Visual Basic. Alternatively, you can press the ALT+F11 key, it will open the Visual Basic editor window.

Secondly, go to the Insert tab >> Module >> Module 1.

Additionally, insert the following code in the Module 1 window.

Public Function TRIMLASTX(rng As String, cnt As Long)
TRIMLASTX = Left(rng, Len(rng) - cnt)
End Function

The code will create a custom function named TRIMLASTX which will trim a defined number of characters from the right side of the selected cell.

Now close the VBA window and type the following formula in cell D5.

=TRIMLASTX(C5,6)

Here, C5 is the selected cell and 6 indicates the number of characters that will be removed.
Gradually, press ENTER and you will see the formula has trimmed the right characters.

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Download Practice Workbook

Trimming Right Characters.xlsm

Conclusion

That’s all about today’s session. And these are some easy methods to trim the right characters and spaces in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.