Remove Trailing Spaces from Cells in Microsoft Excel

The unanticipated discomfort of trailing spaces in Excel cells can frequently result in formatting problems and data mistakes. But don't worry we're here to walk you through a simple method to get rid of those annoying trailing spaces and make sure your data is correct and clear. Millions of people use the robust spreadsheet programme Excel to successfully organise, analyse, and present data. When copying or importing data from different sources, trailing spaces, which are blank spaces at the end of cell contents, might be created unintentionally. They can result in errors in formulae, sorting, and filtering because they are frequently inconspicuous, which makes them difficult to see.

These methods will enable you to clean up your data and preserve its integrity whether your dataset is little or vast. Our instructions are suitable for all skill levels, so you don't need to be an Excel guru to follow along.

Remove Trailing Spaces From Cells

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can remove trailing spaces from cells in Microsoft Excel.

Step 1

Consider an Excel sheet where you have a list of cells with trailing spaces.

First, right-click on the sheet name and select View code to open the VBA application.

Right-click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Sub RemoveTrailingSpaces()
   Dim rng As Range
   Dim cell As Range
   
   ' Prompt the user to select the range
   On Error Resume Next
   Set rng = Application.InputBox(Prompt:="Select the range to remove trailing spaces:", Type:=8)
   On Error GoTo 0
   If Not rng Is Nothing Then
      For Each cell In rng
         If Not IsEmpty(cell.Value) Then
            cell.Value = Trim(cell.Value)
         End If
      Next cell
   Else
      MsgBox "No range selected. The macro will not make any changes.", vbExclamation
   End If
End Sub

Step 3

Then click F5 to run the module. Then select the range of cells and click OK to complete the task. Then you will see that trailing spaces will be removed.

F5 > Select Cells > OK

This is how you can remove trailing spaces in Excel.

Conclusion

In this tutorial, we have used a simple process to learn how you can remove trailing spaces from cells in Microsoft Excel to highlight a particular set of data.