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

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.

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.

Download and try the full feature free trial 30-day

 

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

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.

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.

---

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

Read More... Full Features 30-Day Free Trial... Purchase... 

---

Office Tab - brings tabbed interface to Office, and make your work much easier

• 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!

Read More... Full Features 30-Day Free Trial... Purchase...