3 ways to remove spaces between words / numbers in Excel cells

3 quick ways to remove extra spaces between words or delete all spaces from Excel cells. You can use trim formula, Excel Find & replace or special Excel add-in to clean up cells' content.

When you paste data from an external source to an Excel spreadsheet (plain text reports, numbers from web pages, etc.), you are likely to get extra spaces along with important data. There can be leading and trailing spaces, several blanks between words and thousand separators for numbers.

Consequently, your table looks disorderly and becomes difficult to use. It may be a challenge to find a customer in the Name column since you search for "John Doe" which has no excess spaces between the names while the way it looks in your table is "John Doe". Or numbers can't be summed up, and again extra blanks are the ones to blame.

In this article you'll find how to clean up your data.

Trim blanks between words to 1, remove trailing / leading spaces

For example, you have a table with 2 columns. In the column Name, the first cell contains "John Doe" written correctly without excess spaces. All other cells have extra blanks between the first and the last names. At the same time these cells have irrelevant blanks before and after the full names known as leading and trailing spaces. The second column is called Length and shows the number of symbols in each name:
Excel table with leading and trailing spaces, spaces between words

Get rid of all excess spaces in Excel with a special tool

One click to make your text beautiful
Trim extra spaces Read more

Make your text in Excel beautiful and accurate

8 tools to manage text data
Trim Spaces, Add Text, Change Case, Extract substrings and more Read more

Use the Trim formula to remove extra spaces

Excel has the Trim formula to use for deleting extra spaces from text. Below you can find the steps showing how to use this option:

  1. Add the helper column to the end of your data. You can name it "Trim".
  2. In the first cell of the helper column (C2), enter the formula to trim excess spaces =TRIM(A2)
  3. Copy the formula across the other cells in the column. Feel free to use some tips from Enter the same formula into all selected cells at a time.
  4. Replace the original column with the one that has the cleaned data. Select all cells in the helper column and press Ctrl + C to copy data to clipboard.

    Now pick the first cell in the original column and press Shift + F10 or the menu button  menu keyboard button. Then just press V.
     Replace the original column with the one that has the extra  spaces removed

  5. Remove the helper column.

    That's it! We deleted all excess blanks with the help of the formula trim(). Unfortunately, it's a bit time-consuming, especially if your spreadsheet is rather big.
    Remove spaces between words to 1, remove leading and trailing spaces

    Note: If after using the formula you still see extra spaces (the last cell on the screenshot), please have a look at If the TRIM Function Doesn't Work

Using Find & Replace to remove extra spaces between words

This option needs fewer steps, but allows only deleting excess spaces between words. Leading and trailing spaces will also be trimmed to 1, but will not be removed.

  1. Select one or several columns with the data to delete spaces between words.
  2. Press Ctrl + H to get the "Find and Replace" dialog box.
  3. Press the Space bar twice in the Find What field and once in Replace With
  4. Click on the "Replace all" button, and then press Ok to close Excel confirmation dialog.
    Use Excel Find & Replace to remove extra spaces between words
  5. Repeat step 4 until you see the message "We couldn't find anything to replace." :)

3 clicks to neat data with Trim Spaces tool

If you often import data to Excel from external sources and spend much time polishing up your tables, check out our Text tools for Excel.

The Trim Spaces add-in will clean data imported from the web or any other external source. It removes leading and trailing spaces, excess blanks between words, non-breaking spaces, line breaks, non-printing symbols and other unwanted characters. Also, there is an option to convert words to UPPER, lower or Proper Case. And if you need to change text numbers back to the number format and delete apostrophes, this will not be a problem either.

To remove all extra spaces in your worksheet, including excess paces between words, this is what you need to do:

  1. Download and install a trial version of Ultimate Suite for Excel.
  2. Select the range in your table where you want to remove excess spaces. For new tables, I usually press Ctrl + A to process all columns in a go.
  3. Go to Ablebits Data tab and click on the Trim Spaces icon.
    Click on the Trim Spaces icon to run the tool.
  4. The add-in's pane will open on the left side of your worksheet. Just select the needed checkboxes, click the Trim button and enjoy your perfectly cleaned table.
    All extra spaces are trimmed.

Isn't it faster than with the two previous tips? If you always deal with data processing, this tool will save you hours of precious time.

Want to get this and 300+ other helpful features for your Excel? I'm glad to offer this special opportunity to you:

Promo Code for Ultimate Suite - exclusive offer for our blog readers!

Remove all spaces between numbers

Suppose, you have a workbook with numbers where the digits (thousands, millions, billions) are separated with spaces. Thus Excel sees numbers as text and no math operation can be performed.
Excel cells with numbers where the digits are separated with spaces

The easiest way to get rid of excess spaces is using the standard Excel Find & Replace option:

  • Press Ctrl + Space to select all cells in a column.
  • Press Ctrl + H to open the "Find & Replace" dialog box.
  • Press Space bar in the Find What field and make sure the "Replace with" field is empty.
  • Click on the "Replace all" button, and then press Ok. Voila! All spaces are removed.
     Remove all spaces between numbers

Using formula to remove all spaces

You may need to delete all blanks, like in a formula chain. To do this, you can create a helper column and enter the formula: =SUBSTITUTE(A1," ","")

Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.

Then follow the steps from the part using formula to remove extra spaces between words to 1
Using formula to remove all spaces between numbers

Video: how to remove spaces in Excel

199 responses to "3 ways to remove spaces between words / numbers in Excel cells"

  1. Isaac Yator says:

    Perfect its wonderful

    • Mike says:

      If you copy numbers from a web page, or Microsoft Outlook, you get leading Blank Spaces and then your numbers will Not sum. You have to remove the leading blank spaces for the numbers to sum as numbers should. Go to Data-> Text to Column->Fixed Width and verify that a line appears PRIOR to the copied number data. Then go to "NEXT" and make sure the tab that says, "General", is selected Then Click "FINISH", and all your Non-summing numbers will now sum properly.

  2. samudews says:

    thanks loads!!

  3. md chand says:

    Thanks! a lot remind me

  4. Anonymous says:

    Very helpful. Many thanks

  5. PANKAJ PARTI says:

    we can also use text to column function.

    You just insert two or three columns after that data and select the data and go to data (tools)and select text to column delimited and click on space option and other character if available in that data and after that click next button and now you will be able to remove extra spaces in that data.

    Pankaj Parti (india)

  6. Mesut says:

    Thank you, useful tips

  7. L.Madahali says:

    I have a txt dataset that seems to be seperated with tabs but actually nodes are seperated with space character. I brought it into an excel file and saved it as an csv file then I converted it to an arff file and now I'm openning it in weka but it gives an error to me and say "can not be recognized as an arff file".I don't know what the problem is!

  8. Andrew Mungai says:

    Thanks alot ur a life saver

  9. faizy says:

    please let me know how to make data base on excel?????

  10. Masud says:

    I have 12 Mar 2004 but i need 12-Mar-04.

    Please help

  11. waqar says:

    thanks it's really nice like magic

  12. Anonymous says:

    Oops!! i was just checking for validation. Anyways good Information in here for trimming!!

  13. Chetan says:

    Too good buddy..keep up

  14. dattu says:


  15. Kannan S says:

    Thank you...

  16. Mohammed Azhar says:

    How to remove left space for multiple line in one cell

    John Doe
    John Doe
    John Doe

  17. Prashant Kumar says:

    how to remove multiple space before numbers in excel. 100 cells contain different space.

  18. Mesh says:


  19. sylvanus mfon says:

    it was nice learning new tips but help on how to delect the first six alphabet fro these GAP GRE YTR 453 VM TO YTR453VM ON A ARRAY OF MULTIPLE DATA

  20. NB says:

    Thanks a lot bro.. it was helpful..

  21. Deepak Pawar says:

    Thank you sir

  22. Keshaw says:

    How to remove the space in a column having numbers ending with single space.

  23. Lucille says:

    Well, just to let you know this does not work. Spaces are still there!!!

  24. Steve says:

    I found this tip helpful:

    With your data selected, press Ctrl+H, click in the Find box, hold Alt key and type 0160 on the number keypad. Leave the Replace box blank and press Replace All

  25. sunil says:

    i am copy some data in same sheet and paste in same but same space is coming how to remove i used trim option also but starting some space coming after that word starting. so what to do

  26. Shylendra says:

    how to remove spaces before date.

  27. nemesis_jc04@yahoo.com says:

    not working!

  28. TA says:

    I cannot get this to work if the number starts with a 0, like many UPC codes. It removes the 0 as well. Any suggestions?

  29. ADNAN says:

    How i can remove 12333 back spaces from my data. please answer ASAP thanks

  30. sss says:

    its not working for leading spaces

  31. UGO says:


  32. hugo says:


  33. Bobo says:

    thanks a lot, this rescued my report.

  34. Tarek Alsherif says:

    thank you so much for this post, it helps me a lot!

  35. KathyS says:

    Thank you so much! Extremely helpful.

  36. Felipe says:

    Great post. Thanks!

  37. Rakesh Dey says:

    thanks dude....It will help me a lot

  38. tina says:

    Life saver! My co-worker and I spent the longest time trying to figure out how to get of the spaces between our phone number data! I read your tip and voila- it took me 5 seconds:)

    Thank you!!

  39. ali says:

    92 300 xxxxxxx
    space not clear please help

  40. Kalu says:

    How Many Character in Word & Please Describe and Solution..Plz Help Me

  41. Imran says:

    Commission Fees on Order #3047807249291 how to trim characters from figures by using find and replace option if have different numeric figures Commission Fees on Order #3047807249 please share some trick.

  42. ali says:

    +92 345 5923729

  43. govardhan says:

    how to find the spaces between the string.. I don't want to remove those spaces.. but, just want to know is there any spaces in the string or not..

  44. sivaprakash says:

    Thanks AAAAAAAA LOT... Saves my time and energy.. The Second way...

  45. Kumar says:

    It worked thanks a lot for space

    But can we remove the in between empty space in the coloum

  46. Kittu says:

    Thanq so much sir

  47. Yogesh says:

    Thanks you for the guidance

  48. Jithendra says:

    WOW its working, thank q very much...

  49. Nishanth says:

    hi, I have a column of numbers downloaded from another system, which is in text format, tried all the ways mentioned above but didn't work, could some one please help me out here?

    3 837,87
    1 927,54
    2 208,57
    1 891,05
    1 875,18
    1 880,74
    5 162,41
    66 271,65
    180 815,92
    66 795,78
    5 458,81


  50. MikkoK says:

    Save as Excel 97 - 2003 -workbook (*.xls) format. Now copy the space character from the cell to use in Find and Replace, leave Replace with as blank. Don't know if it matters in which format cells are, only this way worked for me and i have Excel 2016.

  51. Nikhil says:

    Please guide me how to remove space before a Number like below

    " 0.29"
    " 1.459"

  52. Pranee says:

    I try to copy number for web to paste into an excel but i still can't get rid of that space could someone help me please here is the subject.
    (2 917.20 )

  53. hk says:

    how to remove blank cells from the column in excel sheet.

  54. shahid raja says:

    Its amazing,,
    Very helpful to everyone

  55. Ibrahima says:

    Perfect save the day!

  56. tushar says:

    plz find ans. this problem

    you cannot use this command on protected seet to use this command you must first unprotect the seet

  57. Saranya says:

    Thanking you. very useful

  58. Jofrey Khaday says:

    thank you, very helpful

  59. Kurac says:

    Thank you mannn...

  60. Firas says:

    Thanks a lot

  61. omari says:

    this was wonderful. it saved me hundreds of working minutes

  62. kanchan says:

    thanks a lot for your suggestion .

  63. amin says:

    tnx a lot

  64. Segun says:

    Thank you. I just used this to solve a major problem at work now. God bless.

  65. kamlesh says:

    04 100 950105 - this is the original

    04100950105 - i want to make it like this

    • Hi kamlesh,

      You should use the following formula:
      =SUBSTITUTE(A1, " ", "")

      • kamlesh says:

        i hve tried but it is not cming....can u pls guide in formula
        i want to remove space between 04 100 950105 it has to be 04100950105

        • deddy says:

          Try Joel's solution above:
          "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."

  66. kamlesh says:

    Thank you very much Renat Tlebaldziyeu (Ablebits.com Team)

    It worked.......

  67. Dhuha says:

    can I make my number look like $22 instead of $ 22

    • AP says:

      Depending on the version of Excel you are running, if you format the cell as currency, it should automatically remove any unnecessary spaces for you.

      I hope this helps

  68. Senthil Kumar says:

    Precisely I got what I want

  69. Thomas says:

    thanks very much for a good work done

  70. Punith says:

    Select the whole column/width Choose find option/Ctrl "S" select space button and replace it .

  71. abhay says:

    How to remove space before and after of /
    Please help on it.

  72. moin says:

    dear Sir,

    i would like to remove some extra words from one column,

    how to use trim formula here

  73. Samuel Folorunsho says:

    Great job , it really worked for me

  74. XYZ says:


  75. Randhir says:

    Thanks a lot. Very useful.

  76. Gemma says:

    Very helpful.
    Thanks a lot

  77. Todd says:

    Very helpful. This saved so much time.

  78. Shubh says:

    Its very healpfull

  79. Margherita says:

    Reds 19 Joey Votto Red Cool Base Stitched MLB Jersey.Great service of online buy usa soccer sweatshirt,and enjoy
    our cheap nfl authentic jerseys china 79% off.

  80. TOSH GAUR says:

    very nice its works........

    but can you tell me now how to copy and paste this corrected excel worksheet data in another sheet. because its not pasted in another sheet.

    and even we delete the previous and unstructured data then automatically corrected data invisible or unstructured in shape of #REF! this.

    so please help me how to use this corrected data or paste this data in another sheet.


  81. Amit Sundriyal says:


    how can i remove the space, plz help

  82. Mahindra says:


  83. Rajesh Saha says:

    More then one space in front of numbers , how to remove?

  84. Lorena P. Migo says:

    Hi, How am I going to copy paste formula with spaces(filtered) between cells?Thank you!

  85. Shahid Jamil says:

    Perfect... its really wonderful

  86. Pawan Khichi says:

    Thanks, I searched a lot for removing space but it worked with call cleaner add-in Finally.

  87. Chandra Sekhar says:

    I have a problem in my excel

    like 1zw76589246565992 . in right hand side one space is there

    if I try to trim that one it appears trim formula. I don't want it because I want to duplicate the files like 1zw76589246565992 .- 1zw76589246565992

    So please tell me how can I remove the space without appearing formula?...

  88. Chandra Sekhar says:

    I have a problem in my excel

    like 1zw76589246565992 . in right hand side one space is there

    if I try to trim that one it appears trim formula. I don't want it because I want to duplicate the files like 1zw76589246565992 .- 1zw76589246565992

    So please tell me how can I remove the space without appearing formula?...

  89. Doaa says:

    good one

    Thank you

  90. Bharani says:

    I want revmove space 53X 101

  91. Bharani says:

    53 X 101 after replace space i got only 53X 101 . But i want 53X101... Please help

  92. Shyam says:

    remove space between numbers did not work for me!

  93. Gerald says:

    I took the time to actually say thank you this time, usually i don't bother.

  94. Ahamed Jabir says:

    Dear All,

    Kindly help me into provide a formula for the below case.
    I need to delete space after number.

  95. jaga says:

    Thanks buddy

  96. Nathy says:

    thank you!

  97. GIRISH BR says:

    In my excel i don't have Ablebits Data option please suggest how to get it.

  98. Meganathan says:

    Thank you.

  99. CHARLIE says:

    Your tool worked fast and deleted leading and trailing spaces before numbers in a 4500-row spreadsheet! Thanks!

  100. Billelsad says:

    Thank you buddy you saved the day :)

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)