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.

Overview of the article on how to remove trailing spaces in Excel with excel functions and features


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.

Sample Dataset showing first name, last name and full name

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

=TRIM(B5)

B5; is the cell reference.

using TRIM Function to remove extra spaces

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

Using Fill Handle to copy formula to the cells below

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

result of trim function removing trailing spaces in excel

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

=C5&D5

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

using ampersand operator to combine values

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

using fill handle tool to apply formula to the cells below

Note: We can insert a single space between the First and Last Name in the Full Name column. But we are keeping the Full Name column so that you understand how the trailing spaces really appear in any cells.

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

=SUBSTITUTE(B5,CHAR(32),"")

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.

using SUBSTITITE and CHAR Functions to remove trailing spaces

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

applying formula to other cells with fill handleAfter 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.

combining values with ampersand after remove Trailing Spaces in Excel

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

=LEN(B5)-LEN(TRIM(B5))

applying LEN Function to count extra spaces

🔎 Formula Breakdown

LEN(B5); shows the total character numbers of any cell reference.

LEN(TRIM(B5); passes the character number without spaces.

In the end.

LEN(B5)-LEN(TRIM(B5)); results in the character number subtracting the spaces from the total length of the cell reference.

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

Dragging Fill Handle to apply formula

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.

=REPLACE(B5,5,3,"")

The formula declares,

B5; as old_text.

5; as start_num.

3; as num_chars.

“”; as new_text.

using REPLACE Function to remove spaces trailing spaces

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

using Autofill Formula to populate cells below

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.

joining first and last names after removing trailing spaces in Excel


Similar Readings:


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.

Choosing Find and Replace Feature in the Home tab

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.

entring spaces in the Find what field

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.

entering single space in the replace with field

 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.

Results after removing trailing spaces in Excel with find and replace


 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.

dataset for with first, last and full 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).

Choosing Text to Columns feature in the data tab

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

Text to Columns Wizard-1 selecting fixed width option

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

Text to Columns Wizard-2 clicking next button

 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.

Text to Columns Wizard-3

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

results of removing trailing spaces in Excel with text to column option


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

Selecting range of cells

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

Insert 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

VBA Code to remove trailing spaces in excel

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.

Remove Trailing Spaces in Excel with VBA code


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.

Sample Dataset showing full name and password with id

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

=TRIM(C5)

C5; is the cell reference.

Apply Trim function to delete spaces

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

Using fill handle tool

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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo