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

Often while working in Excel, we encounter hasty inputs of trailing spaces in cells. Whatever the reasons it may be, existing trailing spaces pose formula errors in many cases. In this article, we use TRIM, REPLACE, and SUBSTITUTE functions, multiple Excel Features as well as VBA Macro Code to remove trailing spaces in Excel.

Suppose, 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.

Dataset-Remove Trailing Spaces in Excel

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

Dataset for Download

6 Easy Ways to Remove Trailing Spaces in Excel

Method 1: Using TRIM Function

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. The syntax of the TRIM function is

TRIM(text)

Here, text; is the cell reference from where you want to remove the extra spaces.

Step 1: Write the following formula in any adjacent cell (i.e., C4).

=TRIM(B4)

B4; is the cell reference.

trim function-Remove Trailing Spaces in Excel

Step 2: Tab ENTER and Drag the Fill Handle. All the First Names appear, removing all the trailing spaces.

trim function fill handle-Remove Trailing Spaces in Excel

Step 3: Add the Names using the following formula in any adjacent cell (i.e., E4).

=C4&D4

The Ampersand symbol (&) combines the texts in cells C4 and D4.

ampersand

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

trim function final result -Remove Trailing Spaces in Excel

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

Read more: How to Remove Extra Spaces in Excel

Method 2: Using SUBSTITUTE Function

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

SUBSTITUTE(text, old_text, new_text, [instance_num])

Inside the syntax,

text; refers to the cell reference from which you want the trailing spaces substituted.

old_text; the text you want to substitute.

new_text; is the text you want the old_text to substitute with.

Step 1: Paste the below formula in any adjacent cell (i.e., C4).

=(SUBSTITUTE(B4,CHAR(32),””))

In the formula,

B4; is the text.

CHAR(32); refers to the old_text.

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

substitute function-Remove Trailing Spaces in Excel

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

substitute function fill handle

Then, 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.

substitute function final result-Remove Trailing Spaces in Excel

Read more: How to Remove Space Before Text in Excel

Method 3: Using Replace Function

The REPLACE function has a syntax of

REPLACE (old_text, start_num, num_chars, new_text)

The components of syntax,

old_text; refers to the text you want characters to replace with.

start_num; defines the position of the character to replace from old_text.

num_chars; declares the number of the characters you want to replace from old_text.

new_text; is the text that replaces characters in old_text.

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 (i.e., C4).

Step 1: Insert the following formula in any adjacent cell (i.e., C4).

=LEN(B4)-LEN(TRIM(B4))

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

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

In the end.

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

replace function-Remove Trailing Spaces in Excel

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

replace function fill handle

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: Insert an adjacent column of Removed Trailing Spaces. Then Type the below formula in cell D4.

=REPLACE(B4,5,3,””)

The formula declares,

B4; as old_text.

5; as start_num.

3; as num_chars.

“”; as new_text.

replace function

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

replace function fill handle

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

replace function final result-Remove Trailing Spaces in Excel


Similar Readings:


Method 4: Using 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: Go to Home Tab > Find & Select (in Editing section)>Select Replace.

find and replace-Remove Trailing Spaces in Excel

Step 2: Find and Replace window will appear. 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.

find and replace command

Step 3: 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.

find and replace-Remove Trailing Spaces in Excel

 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.

find and replace final result-Remove Trailing Spaces in Excel

 Method 5: Using Text to Column Feature

Text to Column 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, we first insert a spare column beside the First Name column. Because Text to Column replaces the contents adjacent to any column, we apply it to.

text to column-Remove Trailing Spaces in Excel

Step 1: Go to Data Tab > Choose Text to Column (in Data Tools section).

text to column -Remove Trailing Spaces in Excel

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

text to column wizard

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

text to column dividing line

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

text to column preview-Remove Trailing Spaces in Excel

Step 5: Afterward there will be a Warning window saying it wants to replace the adjacent column. Click on OK.

text to column warning window

All the steps come down to a result like a picture below.

text to column final result-Remove Trailing Spaces in Excel

Method 6: Using VBA Macro Code

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: Select a range of cells (i.e., B4:B12).

vba macro code-Remove Trailing Spaces in Excel

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

vba macro code module

Step 3: Paste the following code in the Module.

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

vba macro code

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.

vba macro code final result

Conclusion

In this article, we use TRIM, SUBSTITUTE, and REPLACE functions, multiple Excel features as well as VBA Macro Code to remove trailing spaces. Each method serves well depending on how you want your output data. 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 and 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

ExcelDemy
Logo