How to Remove the Trailing Spaces in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

We can easily remove unwanted characters or symbols from Excel if they are visible to us. But detecting and then deleting spaces is quite hard as they are invisible. In addition to that, things get even harder if the space is a trailing space so to speak. Because there is no such easy way to detect them at the very first glance.

Anyway, the most important fact is that we just simply can’t ignore those trailing spaces. Because those spaces don’t just lie back there in fact, they might cause serious trouble while using formulas in Excel. Thus, it’s safe to remove all of those trailing spaces beforehand. With that said, we will be teaching you how you can remove the trailing spaces in Excel using 2 distinct easy methods with ease.

In this article, we will be using an Excel workbook containing a short movie list to demonstrate the methods to delete trailing spaces in Excel.

how to remove the trailing spaces in excel dataset

So without any further discussion, let’s go through all the methods one by one.

1. Using TRIM Function to Remove Trailing Spaces in Excel 

The shortest and easiest way to delete trailing spaces from the text in Excel is to use the TRIM function. This is a built-in function of Excel that removes all the extra spaces within a text line and therefore restores the text in the normalized form.

🔗 Steps:

Select cell D5.

Type

=TRIM(B5)
within the cell.

delete trailing spaces in excel using trim function

Press the ENTER button.

Congratulations! You have trimmed the trailing spaces from cell B5.

Now drag the Fill Handle icon to the end of the column to normalize all the texts having trailing spaces.

usage of trim function in excel


Excel TRIM Function is not Working: Quick Fix 

There are various kinds of spaces available such as Normal Space, Non-breaking Space, Horizontal Space, Em Space, En Space, etc.

The TRIM function can remove only the normal spaces (Code value 32 in the 7-bit ASCII character set).

It can’t remove other types of spaces such as Non-breaking Space, Horizontal Space, etc.

🔎 Then How to Remove the Other Type of Spaces than the Normal Space?

💬 Substitute the other type of spaces with the normal space and then trim them off.

To do so, follow the steps below:

🔗 Steps:

Select cell D7.

Type

=TRIM(SUBSTITUTE(B7, CHAR(160), " "))
within the cell.

Press the ENTER button.

substitute and trim function in excel nested

🔁 Formula Breakdown

SUBSTITUTE(B7, CHAR(160), ” “) substitutes the Non-breaking Space (Code Value 160) in cell B7 with Normal Space (Code Value 32).

=TRIM(SUBSTITUTE(B7, CHAR(160), ” “)) trims off the substituted Normal Space.

🔎 How to Get the Code Value for Different Types of Spaces?

💬 Use the formula

=CODE(RIGHT(A1,1))
to get corresponding code value for trailing space.

Replace the cell address A1 with the one which contains the other type of spaces than the Normal Space.

Read More: How to Remove Spaces in Excel with Formula (5 Quick Ways)


Similar Readings


2. Removing Trailing Spaces Using Excel VBA

Removing the Trailing Spaces using the TRIM function is quite fair but it might be intimidating in the case of a large number of cells. In those cases, you can use the following VBA codes to delete all the trailing spaces within a selected region of cells.

🔗 Steps:

Select the range of the cells to remove all the trailing spaces.

usage of vba in excel

Press ALT + F11 keys to open the VBA window.

Go to the Insert Module.

Copy the following VBA code:

Sub NoSpaces()

Dim c As Range

For Each c In Selection.Cells

c = Trim(c)

Next

End Sub

Press CTRL + V to paste the above VBA code.

vba code to remove trailing spaces in excel

Press the F5 key to run this code.

This will remove all the trailing spaces from the selected cells instantly.

Read More: How to Remove Spaces in Excel: With Formula, VBA & Power Query


How to Find Trailing Space in Excel (Bonus Tips)

As the trailing spaces are invisible, it’s really tough to spot them at once. You can follow the below steps to find those invisible trailing spaces in Excel.

🔗 Steps:

Select cell D5.

Type the formula

=IF(RIGHT(B5,1)=” “,”Present”,”Absent”)
within the cell.

Press the ENTER button.

find trailing spaces in excel using if function

Drag the Fill Handle icon to the end of the column.

That’s it.

Read More: How to Remove Spaces in Cell in Excel (6 Easy Methods)


Things to Remember

📌 TRIM function removes only the Normal Spaces (Code Value 32).

📌 All other spaces must be converted into the Normal Space first to use the TRIM function.

📌 ALT + F11 is the hotkey to open the VBA window.

📌 F5 is the hotkey to run the VBA code.


Download Practice Workbook

You are recommended to download this Excel workbook and practice along with it.


Conclusion

In this article, we have demonstrated two methods that you can use to delete trailing space in Excel. The first method is using the TRIM function and the second method is using Excel VBA code. Download the attached Excel workbook and practice along with it.


Related Articles

Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo