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.

**Table of Contents**hide

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

`=TRIM(B5)`

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

`=C5&D5`

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.

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

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

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

**🔎**** 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.

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

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

`=TRIM(C5)`

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