How to Remove Trailing Spaces in Excel (6 Easy Methods)

While working in Excel, we often encounter hasty inputs of trailing spaces in cells. Whatever the reasons may be, existing trailing spaces pose formula errors in many cases. This article uses several built-in Excel functions, multiple Excel Features, and VBA Macro Code to remove trailing spaces in Excel.

Before going to the next part, let’s have a quick look at the formulas and features we are going o use in our methods to remove trailing spaces in Excel.

Download Practice Workbook

6 Easy Ways to Remove Trailing Spaces in Excel

Let’s say, in a dataset. We have First Names and Last Names. When we move to compile the Full Name, we encounter extra trailing spaces at the end of the First Names.

We have trailing spaces at the end of First Names. Therefore, we will just remove the trailing spaces from the end of First Names and then compile the Full Names in another column

Let’s figure out 6 easy and effective methods to remove trailing spaces in Excel.

Method 1: Use TRIM Function to Remove Trailing Spaces

The TRIM function removes all the spaces whatever the positions of the spaces may be; leading, middle, and trailing. The TRIM function keeps just only one space in the case of in-between words.

Step 1: First, write the following formula in any adjacent cell (i.e., C5).

B5; is the cell reference.

Step 2: Then, press ENTER and Drag the Fill Handle tool to Autofill the formula below.

Hence, all the First Names appear, removing all the trailing spaces.

Step 3: Next, add the names using the following formula in any adjacent cell (i.e., E5).

The Ampersand symbol (&) combines the texts in cells C5 and D5.

Step 4: After that, hit ENTER and Drag the Fill Handle. You’ll see all extra trailing spaces get removed.

Read more: How to Remove Extra Spaces in Excel

Method 2: Utilizing SUBSTITUTE Function to Delete Trailing Spaces

The Space character is defined by a code, CHAR(32). We can substitute multiple trailing spaces with only a single space via the SUBSTITUTE function.

Step 1: First, type the below formula in any adjacent cell (i.e., C5).

In the formula,

B5; is the text.

CHAR(32); refers to the old_text.

“”; results in a single space at the end of the cell reference B5.

Step 2: Then, press ENTER and Drag the Fill Handle. Afterward, you’ll see all the First Names appear with only one single space.

After that, repeat Steps 3 and 4 from Method 1, you’ll get the compiled names in the Full Name column with a single space between First and Last Names.

Read more: How to Remove Space Before Text in Excel

Method 3: Implementing REPLACE Function

The REPLACE function replaces part of a text string with a different text string.

To find out how many extra trailing spaces are there at the end of the First Names, we use the LEN function in any adjacent cell.

Step 1: First, insert the following formula in any adjacent cell (i.e., C5).

Here, by dragging the Fill Handle, you’ll get all the extra spaces existing in the cells.

The First Names have 4 trailing spaces at the end of them. We’ll replace 3 of them using the REPLACE function and the rest 1 space will appear in between the names.

Step 2: Next, insert an adjacent column of Removed Trailing Spaces. Then type the below formula in cell D5.

The formula declares,

B5; as old_text.

5; as start_num.

3; as num_chars.

“”; as new_text.

Step 3: Now, press ENTER then Drag the Fill Handle; all the First Names entries will pop up without any trailing spaces.

Here, we replace the cell reference from B5 to D5 in the Ampersand (&) formula in the Full Name column. All the trailing spaces in full names get removed.

Similar Readings:

• Remove All Spaces in Excel (9 Methods)
• How to Remove Leading Space in Excel (5 Useful Ways)
• Remove Blank Spaces in Excel (7 Ways)

Method 4: Removing Trailing Spaces with Find and Replace Feature

From the previous method (Method 3) we are aware that the First Names have 4 trailing spaces at their end. We can remove the 3 trailing spaces using Excel’s FIND and Replace feature and leave 1 trailing space to appear in between the Names.

Step 1: First, go to Home Tab > Find & Select (in Editing section) > Select Replace.

Step 2: Now, Find and Replace window will appear. Here, insert 4 spaces in the Find What command box and only 1 space in Replace with command box. Click on Find Next. You can see Excel shows where it finds 4 spaces.

Step 3: Next, click on Replace All. All the extra spaces go away showing Extra Spaces number 1. A pop-up window appears saying that it has made 9 replacements. Click OK.

 After closing the Find and Replace window, you can see the changes in the Full Name column similar to the image below. It’s certain that all the extra trailing spaces get omitted.

 Method 5: Using Text to Columns Feature

Text to Columns feature divides text using delimiters. We can use this feature to remove all the trailing spaces from the end of the First Names.

In order to apply this feature, follow the steps below.

Step 1: First, go to Data Tab > Choose Text to Columns (in Data Tools section).

 Step 2: Then, Convert Text to Column wizard will open. In that wizard, Select Fixed Width then Click on Next.

Step 3: Here, from the Data preview, Place the dividing line as close as possible of the highest wide entries. Again, click on Next.

 Step 4: Now, choose the Column data format as Text. You can see the Data preview that will be the final output. Click on Finish.

Hence, all the steps come down to a result like a picture below.

Method 6: Applying VBA Code to Erase Trailing Spaces in Excel

VBA Macro Code is very effective when it comes to doing tedious work. In this case, we can use the TRIM command via VBA Macro Code to remove trailing spaces from a selected range.

Step 1: First, select a range of cells (i.e., B5:B13).

Step 2: Then, press ALT+F11 altogether. Microsoft Visual Basic window opens up. From the Toolbar, Click on Insert> Select Module.

Step 3: After that, paste the following code in the Module.

Sub RemovingTrailingSpaces()
Dim val As Range
For Each val In Selection.Cells
val = Trim(val)
Next
End Sub

Step 4: Hit F5 to run the code. Back to the worksheet, you’ll see all the trailing spaces from First Names disappear. And the whole thing looks like the image below.

How to Remove Trailing Space for Numbers in Excel

Sometimes you might be in a situation to remove trailing spaces from numbers in Excel. Let’s consider the following dataset.

The password of the individual persons consists of some number and alphabets where numbers and alphabets are separated by spaces which we want to remove.

Let’s apply the following steps to do this task.

Step 1: First, write the following formula in any adjacent cell (i.e., D5).

C5; is the cell reference.

Step 2: Then, tab ENTER and Drag the Fill Handle.

All the passwords appear removing all the trailing spaces after numbers.

Conclusion

In this article, we use TRIM, SUBSTITUTE, and REPLACE functions, multiple features as well as VBA Macro Code to remove trailing spaces in Excel. Here, each method serves well depending on how you want your output data. In fact, the TRIM, SUBSTITUTE, and REPLACE functions offer multiple options in their outputs whereas Excel features are rigid in their outputs. Hope the above-mentioned methods do your job. Comment, if you have further queries or have anything to add.

Further Readings

• Remove Spaces in a Cell in Excel (5 Ways)
• How to Remove Leading Spaces in Excel (4 Methods)