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

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.

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

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

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.

To do so, follow the steps below:

🔗 Steps:

❶ Select cell D7.

❷ Type

❸ Press the ENTER button.

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

💬 Use the formula

▶ 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

• How to Remove White Space in Excel (6 Easy Ways)
• Remove Space in Excel Before Numbers (3 Easy Ways)
• How to Remove Leading Space in Excel (5 Useful Ways)
• Remove All Spaces in Excel (9 Suitable Ways)
• How to Remove Space Before Text in Excel (4 Methods)

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.

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

❻ 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

❸ Press the ENTER button.

❹ 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

• How to Remove Tab Space from Excel (5 Easy Methods)
• Remove Space between Rows in Excel (5 Methods)
• How to Remove Space after Number in Excel (6 Easy Ways)
• Remove Space in Excel after Text (6 Quick ways)
• How to Remove Blank Spaces in Excel (7 Ways)
• How to Remove Leading Spaces in Excel (4 Easy Methods)