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.

Let’s say we have a dataset where 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

Download Practice Workbook

5 Methods to Trim Right Characters

1. TRIM function to Remove Extra Spaces From the Right

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 name,

dataset with spaces

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

=TRIM(A7)

Here, the TRIM  function will remove the spaces from the right end

trim

Press ENTER to remove all the extra spaces.

trim right spaces

Drag cell C7 to apply the same formula in all other cells.

all

2. Text to Columns Features 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.

spare column

Now go to Data> Data Tools and select Text to Columns.

TEXT TO COLUMN

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

TEXT TO COLUMN

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

TEXT TO COLUMN

Now you will see, all of your data is highlighted with black color. Select Finish.

TEXT TO COLUMN

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

TEXT TO COLUMN

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

trim right spaces

3. LEFT and LEN function to trim Right Characters

With a combination of the LEFT function and the LEN function you can easily trim right characters from your data cells. Type the following formula in an empty cell (C7),

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

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

trim right characters

Press ENTER and you can see the right characters which are miles for our dataset have been removed.

trim right characters

Drag cell C7 to apply the same formula in all other cells.

trim right characters

4. Getting Numeric Value by Trimming Right Characters

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 C7,

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

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

value left len

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, C7 which indicates the return is a numeric value.

trim right characters

Drag the C7 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.

trim right characters

5. Trim Right Characters Using VBA

Another way to trim the right characters in Excel is making a custom function using Visual Basic Applications (VBA). First press ALT+F11, it will open the VBA window. Now right click on the sheet from the left panel to open a dropdown menu. Expand Insert by clicking on it and selecting Module.

vba

It will open a Module(Code) window.

module

Insert the following code in the Module(Code) 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.

vba code

Now close the VBA window and type the following formula in cell C7,

=TRIMLASTX(B7,6)

Here, B7 is the selected cell and 6 indicates the number of characters which will be removed.

custom function

Press ENTER and you will see the formula has trimmed the right characters.

trim right characters

Drag cell C7 to apply the same formula in all other cells.

trim right characters

Conclusion

You can trim characters from the right in Excel by any of the above methods. If you face any kind of confusion, please leave a comment.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

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?

  2. Reply
    Md. Abdur Rahim Rasel Aug 30, 2022 at 1:25 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

ExcelDemy
Logo