Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.


Quickly remove all spaces (extra spaces, leading spaces or trailing spaces) from cells:

The Remove Spaces utility of Kutools for Excel can help you quickly remove all spaces from cells of selected range as below screenshot. Besides, you can also apply the feature to only remove leading spaces, trailing spaces or extra spaces from selected cells.
Download the full feature 60-day free trail of Kutools for Excel now!


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, increasing productivity by 70%.

Download and try the full feature free trial 60-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.

 Tip. If you want to have a free trial (60-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
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Da345 · 5 months ago
    I removed all spaces with the Replace function, very easy, thank you
  • To post as a guest, your comment is unpublished.
    javad · 8 months ago
    thank you a lot

    it was very god
  • To post as a guest, your comment is unpublished.
    Rafik · 1 years ago
    Thank you a lot.
  • To post as a guest, your comment is unpublished.
    Ocaya · 2 years ago
    Excellent, Very helpful
  • To post as a guest, your comment is unpublished.
    alefpe · 2 years ago
    Thank you very much.
    It was so helpful.
  • To post as a guest, your comment is unpublished.
    alfonso · 2 years ago
    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.
  • To post as a guest, your comment is unpublished.
    ArvRajB · 2 years ago
    Thank you very much!!
  • To post as a guest, your comment is unpublished.
    Cecep Saefulloh · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Joseph Wokwera · 2 years ago
    Thanks so much helpful. i ve been trying this for 2 days. it worked
  • To post as a guest, your comment is unpublished.
    PRAMOD · 2 years ago
    Excellent.Very helpful to remove space in a cell
  • To post as a guest, your comment is unpublished.
    PRAMOD · 2 years ago
    Excellent, nice formula to substitute or remove the spaces in a cell.
  • To post as a guest, your comment is unpublished.
    sachin · 2 years ago
    Thank You Very Much. help me a lot..
  • To post as a guest, your comment is unpublished.
    SUDHIR MISHRA · 3 years ago
    EXCELLENT :) THANK FOR HELP
  • To post as a guest, your comment is unpublished.
    Kosova · 3 years ago
    Thanks a lot, now its so easy.
  • To post as a guest, your comment is unpublished.
    RDM · 3 years ago
    THANK YOU! Really helped me and my bandwidth
  • To post as a guest, your comment is unpublished.
    Sharil · 3 years ago
    Oh its amazing, i didn't knew about to remove the space between words in a cell....its so easy...thanku so much.
  • To post as a guest, your comment is unpublished.
    Ashok Kumar R · 3 years ago
    :-) good.. any move issue please mail me
  • To post as a guest, your comment is unpublished.
    Dinesh · 3 years ago
    i am using MS 2010 ,how to remove all space in excel. exmple also give
  • To post as a guest, your comment is unpublished.
    JUNAID · 3 years ago
    Thanks a lot for helping us!!!!!!!!! :-)
  • To post as a guest, your comment is unpublished.
    JUNAID · 3 years ago
    THANKS A LOT FOR HELPING US!!!!!!!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Suresh · 4 years ago
    Thanks a lot for sharing this Tool :-)
  • To post as a guest, your comment is unpublished.
    KD · 4 years ago
    Thank You. Your help has reduced my effort a lot.
  • To post as a guest, your comment is unpublished.
    Henman · 4 years ago
    Neither of these suggestions work... except, I'm sure, the Kutools solution so conveniently suggested. What an awesome blog. Thanks.
  • To post as a guest, your comment is unpublished.
    Mr.Niekoo · 4 years ago
    How i can remove dots from my columns of excel.I want to remove all dots from full list of columns.The data consist on Phone no's list

    email me
    mr.niekoo@hotmail.com
  • To post as a guest, your comment is unpublished.
    rajesh · 4 years ago
    Great command, It is very usefull.
  • To post as a guest, your comment is unpublished.
    Maria · 4 years ago
    I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?
    • To post as a guest, your comment is unpublished.
      Imran · 3 years ago
      [quote name="Maria"]I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?[/quote]
      Select the column--> go to Data> Text to column> select delimited> next> next> change the date format as MDY or DMY as the case in your sheet> press fininsh.
  • To post as a guest, your comment is unpublished.
    M&M · 4 years ago
    Replace option is the best and easiest! Thanks for sharing the tips!
  • To post as a guest, your comment is unpublished.
    Yasar Arafath · 4 years ago
    Remove space before and after the cell content (Eg:___26350__)
    We can use this formula =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
    • To post as a guest, your comment is unpublished.
      Manish · 4 years ago
      Thanks a lot:):):).... It saved a lot of time....keep on updating such useful and very rate instances!!!!!!
  • To post as a guest, your comment is unpublished.
    Sushen · 4 years ago
    Very use full command
  • To post as a guest, your comment is unpublished.
    carla · 4 years ago
    Thank you! very helpful. saved the day.
  • To post as a guest, your comment is unpublished.
    kirtan · 4 years ago
    tried all the options above. not working as there are spaces before and after number. Any other alternative?
  • To post as a guest, your comment is unpublished.
    karan · 4 years ago
    very helpful :) and easy to understand

    ;-)
  • To post as a guest, your comment is unpublished.
    Anna · 4 years ago
    Very helpful!
    Thank you
  • To post as a guest, your comment is unpublished.
    GaryMonday · 5 years ago
    Thank you very much for the trick, very very useful, it makes my spreadsheet a lot neater.

    Thanks again.

    Gary
  • To post as a guest, your comment is unpublished.
    Prabhakar · 5 years ago
    Wow its fantastic small commands work very well
  • To post as a guest, your comment is unpublished.
    ilham · 5 years ago
    wow thanks ..useful :-) :):):):):):)
  • To post as a guest, your comment is unpublished.
    BISHNU · 5 years ago
    I like this tools very much
  • To post as a guest, your comment is unpublished.
    KALPESH SUTHAR · 5 years ago
    Thanks a lot....its really helpful..save lot of time... :-)
  • To post as a guest, your comment is unpublished.
    corpsman0000 · 5 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Avi · 5 years ago
      Convert the cell into text format then use replace function
  • To post as a guest, your comment is unpublished.
    Ranjith Kumar · 5 years ago
    Thanks Very very use full
  • To post as a guest, your comment is unpublished.
    Eva · 5 years ago
    Thanks for finally talking about >How to remove spaces between character and numbers within cells in Excel?
  • To post as a guest, your comment is unpublished.
    Rebeccah · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Stefan · 5 years ago
    Thank you, easy to understand. best on the web
  • To post as a guest, your comment is unpublished.
    Janardhan · 5 years ago
    Very easy to understand. Good to follow.
    • To post as a guest, your comment is unpublished.
      ashutosh · 4 years ago
      bullshit....doesn't work
      • To post as a guest, your comment is unpublished.
        Joel · 3 years ago
        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.
        • To post as a guest, your comment is unpublished.
          Ramesh Kumar P · 2 years ago
          It really worked....I tried may function like Clean, Trim, Substitute, etc
        • To post as a guest, your comment is unpublished.
          Jay · 2 years ago
          THAT WORKED! Thanks man
        • To post as a guest, your comment is unpublished.
          kati · 3 years ago
          thanks soo much. you are a genius!!!!
        • To post as a guest, your comment is unpublished.
          Imran · 3 years ago
          [quote name="Erika"]It worked for me! Thank you![/quote]
          Thank you so much for that. This saved a lot of time
        • To post as a guest, your comment is unpublished.
          Erika · 3 years ago
          It worked for me! Thank you!
  • To post as a guest, your comment is unpublished.
    Muhibullah Afzalzada · 5 years ago
    Thanks a lot, the Replace option worked as a charm... you really saved loads of my time.
  • To post as a guest, your comment is unpublished.
    Muhibullah Afzalzada · 5 years ago
    Thanks a lot, I really needed and the Replace option was a charm.
    • To post as a guest, your comment is unpublished.
      Stephen · 3 years ago
      Thanks a lot for the explanation - saved our dept a lot of work.