Maruf Islam
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.
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.
Step 2: Tab ENTER and Drag the Fill Handle. All the First Names appear, removing all the trailing spaces.
Step 3: Add the Names using the following formula in any adjacent cell (i.e., E4).
The Ampersand symbol (&) combines the texts in cells C4 and D4.
Step 4: Hit ENTER and Drag the Fill Handle. You’ll see all extra trailing spaces get removed.
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
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).
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.
Step 2: Press ENTER and Drag the Fill Handle. Afterward, you’ll see all the First Names appear with only one single space.
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.
Read more: How to Remove Space Before Text in Excel
Method 3: Using Replace Function
The REPLACE function has a syntax of
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); 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.
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: Insert an adjacent column of Removed Trailing Spaces. Then Type the below formula in cell D4.
The formula declares,
B4; as old_text.
5; as start_num.
3; as num_chars.
“”; as new_text.
 Step 3: Press ENTER then Drag the Fill Handle; all the First Names entries will pop up without any trailing spaces.
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.
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: 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.
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.
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.
 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 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.
Step 1: Go to Data Tab > Choose Text to Column (in Data Tools section).
 Step 2: Convert Text to Column wizard will open. In that wizard, Select Fixed Width then Click on Next.
Step 3: From the Data preview, Place the dividing line as close as possible of the highest wide entries. Again, click on Next.
 Step 4: Choose the Column data format as Text. You can see the Data preview that will be the final output. Click on Finish.
Step 5: Afterward there will be a Warning window saying it wants to replace the adjacent column. Click on OK.
All the steps come down to a result like a picture below.
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).
Step 2: Press ALT+F11 altogether. Microsoft Visual Basic window opens up. From the Toolbar, Click on Insert> Select 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
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.
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.
