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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview Image


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.

Dataset for trim right characters

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.

Extra Spaces

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.

Utilizing TRIM Function to Remove Extra Spaces

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.

Employing Text to Columns Feature to Trim Right Spaces in Excel

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

Text to columns feature

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

Convert Text to Column Wizard to trim right in excel

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

Convert Text to Column Wizard step2 window

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

Convert Text to Column Wizard step 3

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

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

removed extra spaces


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.

Using LEFT and LEN Functions to Trim Right Characters

Read More: How to Trim Part of Text in Excel 


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.

Applying VALUE, LEFT, and LEN Functions to trim right


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.

Trim Right Characters Incorporating VBA Macros

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

Inserting module in Excel

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

Trimlastx function to trim right in Excel

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

TRIMLASTX function


Practice Section

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

Practice Section


Download Practice Workbook


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.


Further Readings


<< Go Back to Excel TRIM Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

2 Comments
  1. both trim and text to columns did not work i have a data set like yours in column A Which has an uneven amount of characters, but the same number of space to the right (2 spaces) so text to columns wouldn’t work for all of them and trim didn’t seem to work properly any other ideas to just have excel remove 2 characters from the right?

    • Reply Avatar photo
      Md. Abdur Rahim Rasel Jun 21, 2023 at 12:33 PM

      Hi TONY!
      We checked the Excel file. It is working fine for an even and uneven amount of characters. Make sure the TRIM function and the text to the column are entered correctly. We suggest you download the file and practice there. If you are unable to solve your problem, you can also share your Excel file with us.
      Thank you for being with us.

      Regards
      Md. Abdur Rahim Rasel(Exceldemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo