## Remove spaces in Excel cells – leading spaces, trailing spaces, extra spaces or all spaces

Sometimes, when data has been copied and pasted from other applications into a worksheet, something may go wrong if extra spaces unconsciously coming along with the data. For example, when adding two number cells which including leading spaces, it will return the result as 0 instead of the correct result; Or get the wrong result while comparing two cells with the same content but one of them containing leading or trailing spaces. How to remove these spaces? This tutorial will introduce several methods for you.

**Remove extra spaces including leading, trailing and excess in-between spaces from cells**

**Remove only leading spaces from cells**

**Remove only trailing spaces from cells**

**Remove all spaces from cells**

**Easily remove leading, trailing, extra and all spaces from cells with an amazing feature**

**Remove extra spaces including leading, trailing and excess in-between spaces from cells**

Supposing you have a column list of cells containing leading, trailing, and excess in-between spaces. For removing all these extra spaces from cells, the TRIM function can help.

**=TRIM(TEXT)**

Select a blank cell, enter **=TRIM(B3)** into it and press the **Enter** key. And then drag its **Fill Handle** down to apply the formula to other cells. See screenshot: **Note**: B3 is the cell containing the text from which you want spaces to be removed.

Now all extra spaces including leading, trailing, and excess in-between spaces are removed from the specified cells.

**Note**: This operation needs to be done in a new column. After removing all extra spaces, you need to replace the original data with the trimmed data by pressing the **Ctrl** + **C**, select the original data range and right click to select **Values** under the **Paste Options** section.

**Several Clicks To Remove Unneeded Spaces From Cells**

With the **Remove Spaces** utility, only several clicks can help you removing not only **leading spaces, trailing spaces, excess spaces** but also **all spaces** from a range, multiple ranges or even the whole worksheet, which will save a lot of your working time.**Kutools for Excel** - Gathers more than 300 powerful advanced features, designed for 1500+ work scenarios, solve 80% Excel problems.

**Remove only leading spaces from cells**

In some cases, you may need to remove the leading spaces only and keep all in-between spaces in cells as the below screenshot shown. This section introduce another formula and VBA code to solve this problem.

##### Use MID function to remove leading spaces only

Select a blank cell (here I select cell D3), copy the below formula into it and press the **Enter** key. And then drag the **Fill Handle** down to apply the formula to other cells.

**=MID(B3,FIND(MID(TRIM(B3),1,1),B3),LEN(B3))**

In this formula, **B3** is the cell containing the text from which you want leading spaces to be removed.

**Note**: This operation needs to be done in a new column. After removing all leading spaces, you need to replace the original data with the trimmed data without formulas.

##### Use VBA code to remove leading spaces only

1. Open the worksheet contains the cells you will remove leading spaces from, and then press the **Alt** + **F11** keys to open the **Microsoft Visual Basic for Applications** window.

2. Click **Insert** > **Module**, then copy below VBA code into the Module window.

```
Sub RemoveLeadingSpaces()
'Updateby20190612
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub
```

3. Press the **F5** key to run the code. A **Kutools for Excel** dialog pops up, please select the continuous cells you will remove leading spaces from, and then click the **OK** button.

Now you can see only the leading spaces are removed from the selected cells.

**Remove only trailing spaces from cells**

1. Open the worksheet contains the cells you will remove leading spaces from, and then press the **Alt** + **F11** keys to open the **Microsoft Visual Basic for Applications** window.

2. Click **Insert** > **Module**, then copy below VBA code into the Module window.

```
Sub RemoveTrailingSpaces()
'Updateby20190612
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.RTrim(Rng.Value)
Next
End Sub
```

3. Press the **F5** key to run the code. A **Kutools for Excel** dialog pops up, please select the continuous cells you will remove trailing spaces from, and then click the **OK** button. Now you can see only the trailing spaces are removed from the selected cells.

**Remove all spaces from cells**

If you want to get rid of all spaces in specific cells, methods in this section can do you a favor.

##### Use SUBSTITUTE function to remove all spaces from cells

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

Select a blank cell, copy the below formula into it and press the **Enter** key. Then drag the **Fill Handle** down to apply the formula to other cells.

**=SUBSTITUTE(B3," ","")**

**Tips**: In this formula, **B3** is the cell containing text for which you want to remove all spaces;

**" "** (a space enclosed by double quotation marks) here represents the spaces you want to remove;

**""** here means that you will replace all spaces with nothing.

Then you can see all spaces in specific cells are removed immediately.

**Note**: This operation needs to be done in a new column. After removing all spaces, you need to replace the original data with the trimmed data without formulas.

##### Use Find & Replace function to remove all spaces from cells

Besides the above **SUBSTITUTE** function, the Find & Replace feature can also help to remove all spaces from cells.

1. Select the cells containing spaces you will remove, press the **Ctrl** + **H** keys to open the **Find and Replace** dialog box.

2. In the** Find and Replace** dialog and under the **Replace** tab, type one space into the **Find what** textbox, keep the **Replace with **textbox empty, and then click the **Replace All** button.

**Easily remove leading, trailing, extra and all spaces from cells with the Kutools**

**Tip**: If you are fed up with using formulas and VBA codes to remove spaces, the **Remove Spaces** utility of **Kutools for Excel** is your best choice. With only several clicks, you can remove not only leading spaces, trailing spaces, excess spaces but also all spaces from a range, multiple ranges or even the whole worksheet, which will save a lot of your working time.

Before using the Kutools for Excel, you need to take minutes to **free download and install it** firstly.

1. Select a range or multiple ranges with the cells you will remove spaces from, click **Kutools** > **Text** > **Remove Spaces**. See screenshot:

2. You can see there are 5 options in the **Remove Spaces** dialog box:

- To only remove the leading spaces, please select the
**Leading spaces**option; - To only remove the trailing spaces, please select the
**Trailing spaces**option; - To remove the leading spaces and the trailing spaces at the same time, please select the
**Leading & Trailing spaces**option; - To remove all extra spaces (including leading, trailing, excess in-between spaces), please select the
**All excess spaces**option; - To remove all spaces, please select the
**All spaces**option.

Then click the **OK** button to start the operation.

If you want to have a free trial (30-day) of this utility, **please click to download it**, and then go to apply the operation according above steps.

#### Other practical cases related to Excel spaces

Besides removing spaces from cells, have you ever faced the circumstances of counting, adding spaces, or replace the spaces with other characters in cells? The below recommendation might speed your Excel work.

**Count number of total spaces in a cell**

Before removing all spaces from a cell, you may be curious about how many spaces existing in it. This tutorial provides methods in detailed steps to help you quickly get the total count of spaces in a cell.**Click to know more...**

**Add space after each comma in specific Excel cells**

Sometimes, spaces might be removed accidentally from specific cells. This tutorial is talking about adding a space after every comma to make the text string more clearly and standard with detailed steps.**Click to know more...**

**Add spaces between numbers in Excel cells**

This tutorial is talking about adding space between each number or every nth numbers in Excel cells. Supposing you have a column of phone numbers, and you want to add spaces between them to make the number looks more intuitive and easier reading. Methods in this tutorial will help.**Click to know more...**

**Add spaces before capital letters in Excel cells**

This tutorial is talking about adding a space before every capital letters in Excel cells. Supposing you have a list of text strings with all spaces removed accidentally, like this: InsertBlankRowsBetweenData, to add the spaces before each capital letters to separate the words as Insert Blank Rows Between Data, try the methods in this tutorial.**Click to know more...**

**Replace space with specific character in Excel cells**

In many cases, you prefer to replace spaces with specific characters rather than remove them from cells directly. Here provides methods for you to easily replace space with underscore, dash or comma in cells.**Click to know more...**

## The Best Office Productivity Tools

### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!