Skip to main content

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.

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

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

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


  • 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...
kte tab 201905
  • 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!
officetab bottom
Comments (58)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I removed all spaces with the Replace function, very easy, thank you
This comment was minimized by the moderator on the site
thank you a lot

it was very god
This comment was minimized by the moderator on the site
Thank you a lot.
This comment was minimized by the moderator on the site
Excellent, Very helpful
This comment was minimized by the moderator on the site
Thank you very much. It was so helpful.
This comment was minimized by the moderator on the site
if it doesnt work for you. First you remove the spaces, then you remove the letters, for example: i have USD 1234.00 , first i do the find & replace just the space between USD and 1234.00, now i have USD1234.00, now i go back to find & replace and on find what: i put USD ( no spaces ), and nothing on REPLACE WITH: then i click on Replace all , and i have now 1234.00 if you do it the other way it doesnt work i dont know why.
This comment was minimized by the moderator on the site
Thank you very much!!
This comment was minimized by the moderator on the site
Great Tips especially for research keywords and to make a lot of hashtags on facebook, and You have great tools to make it simple It is appropriate that we visit each other and communicate Web Development Agency Konsultan Blog Teknologi
This comment was minimized by the moderator on the site
Thanks so much helpful. i ve been trying this for 2 days. it worked
This comment was minimized by the moderator on the site
Excellent.Very helpful to remove space in a cell
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations