Mrinmoy Roy
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.
Download the Practice Workbook
You are recommended to download this Excel workbook and practice along with it.
2 Ways to Remove Trailing Spaces in Excel
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. Delete Trailing Spaces in Excel using TRIM Function
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)
❸ 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.
Quick Fix: TRIM Function is not Working
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), " "))❸ Press the ENTER button.
🔁 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))▶ Replace the cell address A1 with the one which contains the other type of spaces than the Normal Space.
Similar Readings:
- How to Remove Blank Spaces in Excel (7 Ways)
- Remove All Spaces in Excel
- How to Remove Leading Space in Excel (5 Useful Ways)
2. Remove Trailing Spaces using 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.
Bonus Tips: Find Trailing Space in Excel
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”)❸ Press the ENTER button.
❹ Drag the Fill Handle icon ▶ to the end of the column.
That’s it.
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.
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.
