Note: The other languages of the website are Google-translated. Back to English
English English

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
Thanks a lot, I really needed and the Replace option was a charm.
This comment was minimized by the moderator on the site
Thanks a lot for the explanation - saved our dept a lot of work.
This comment was minimized by the moderator on the site
Thanks a lot, the Replace option worked as a charm... you really saved loads of my time.
This comment was minimized by the moderator on the site
Very easy to understand. Good to follow.
This comment was minimized by the moderator on the site
bullshit....doesn't work
This comment was minimized by the moderator on the site
Didnt work for me too, however found the right way. Select and highlight the space you want to get rid of. Copy this space and select the find and replace. Paste this space in find and replace with nothing. I guess it wasnt a space in the first place. Whatever it was you now find it by copying whatever it was. Hope it works for you.
This comment was minimized by the moderator on the site
It worked for me! Thank you!
This comment was minimized by the moderator on the site
[quote]It worked for me! Thank you!By Erika[/quote] Thank you so much for that. This saved a lot of time
This comment was minimized by the moderator on the site
thanks soo much. you are a genius!!!!
This comment was minimized by the moderator on the site
THAT WORKED! Thanks man
This comment was minimized by the moderator on the site
It really worked....I tried may function like Clean, Trim, Substitute, etc
This comment was minimized by the moderator on the site
Thank you, easy to understand. best on the web
This comment was minimized by the moderator on the site
Find/replace doesn't work for me, which is why I'm googling this topic in the first place. Is there a setting somewhere that disables this? I want to delete ":" form the cells in a column (and "/" from the cells in another column). I ought to be able to highlight the column, ^H, type ":" (or "/") in the find field, leave the replace field blank, and click Replace All, and it should do it. Or Find Next/Replace/Replace/Replace through the cells one at a time. But it advances therough the cells but doesn't do anything. If I put something in the replace field, it will do the replace, but it won't replace with an empty string.
This comment was minimized by the moderator on the site
Thanks for finally talking about >How to remove spaces between character and numbers within cells in Excel?
This comment was minimized by the moderator on the site
Thanks Very very use full
This comment was minimized by the moderator on the site
how do you remove spaces without removing the zeros that in the begining of values middle and end? i just want to remove the spaces in between the values only. i tried the above and the zeros disappeared.
This comment was minimized by the moderator on the site
Convert the cell into text format then use replace function
This comment was minimized by the moderator on the site
Thanks a lot....its really helpful..save lot of time... :-)
This comment was minimized by the moderator on the site
I like this tools very much
This comment was minimized by the moderator on the site
wow thanks ..useful :-) :):):):):):)
This comment was minimized by the moderator on the site
Wow its fantastic small commands work very well
This comment was minimized by the moderator on the site
Thank you very much for the trick, very very useful, it makes my spreadsheet a lot neater. Thanks again. Gary
This comment was minimized by the moderator on the site
Very helpful! Thank you
This comment was minimized by the moderator on the site
very helpful :) and easy to understand ;-)
This comment was minimized by the moderator on the site
tried all the options above. not working as there are spaces before and after number. Any other alternative?
This comment was minimized by the moderator on the site
Try this one =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
This comment was minimized by the moderator on the site
Thank you! very helpful. saved the day.
This comment was minimized by the moderator on the site
Very use full command
This comment was minimized by the moderator on the site
Remove space before and after the cell content (Eg:___26350__) We can use this formula =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
This comment was minimized by the moderator on the site
Thanks a lot:):):).... It saved a lot of time....keep on updating such useful and very rate instances!!!!!!
This comment was minimized by the moderator on the site
Replace option is the best and easiest! Thanks for sharing the tips!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations