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

3 quick ways to remove extra spaces between words to 1 or to delete all spaces from Excel cells. Using trim formula, using Excel Find & Replace, using special Excel add-in to clean up cells' content. Continue reading →

Comments page 4. Total comments: 265

  1. Thanks a lot. It helped me tons. Cheers!!!

  2. A very handy way to remove spaces. Thanks so much, and keep it up.

  3. "
    111013491504"
    "
    111013491515"
    "
    123016013840"
    "
    111013491504"
    "
    1029232644"
    "
    1029207094"
    "
    1029183115"
    "
    1029212005"
    "
    1029211950"
    how can remove this space

    1. select column
      Press Ctrl H to open the "Find & Replace"
      Press " 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.
      Select column
      press F, dialog box opens, then click on special, then click on blank and click on OK
      NOW LEFT CLICK ON BLANK CELL and select delete
      select Shift cell up and press OK.

  4. This will save me a huge problem from my boss. Huge data supplied came with spaces between phone numbers and we needed to contact them via SMS. Thanks in advance

  5. This article saved me alot of time and frustration.

    1. 1. select the whole column or the cell you want to convert to numbers
      2. Right click and then press "Format Cells"
      3. click on "Numbers" and adjust the number of decimals you want in the 'Decimal Places'

  6. How to remove spaces in excel
    Ex: i have an excel sheet which is in 10 thousand phone numbers in a sheet but the sheet having more spaces.spaces means not a front space and back space,only space from up or above space
    like phone number above space and number like this 9999999999,how to remove above space
    Space
    9999999999----> phone number
    Please tell me the formula

    1. Find and replace then press CTRL+J on replace then click the Replace All button.

      1. =SUBSTITUTE(C13," ","")

  7. I can still see trailing spaces on my spreadsheets after removing them via Find and replace method. Please help

    1. I had the same issue. Here is what u can do, select (highlight) the "space" after the data and copy (ctrl+c). Then in find and replace, paste (ctrl+v) the "space" that u have copied into the "Find What" and leave the "Replace With" empty.
      Good Luck!
      IG:Mokhschild

      1. Great! I got it after trying with different methods as mentioned in this column. Thanks!

      2. Nothing else was working - this did! Thankyou!

      3. Update - tested this same method with the Substitute formula - it works, too!

      4. I literally cannot thank you enough for this! I spent over a half hour trying everything short of physically clicking each cell and removing the space after my numbers. This is, as another commenter said, brilliant!

      5. You saved meeeee!!!!! many thanks

      6. GREAT! That was exactly what I needed to do to erase the pesky space at the beginning of each cell in my sheet. Thanks!

      7. Thank you as this thread just fixed my problems. I would be interested to know what it was that caused that pseudo-space as it was clearly a space to me.

      8. Super, thanks!

      9. Excellent! It helped a lot. Thanks, thanks...

      10. Brilliant, just fixed my problems too! Seems the "spaces" weren't spaces

        1. I have looked for hours and hours a solution to replace a "space" that was not a space. And your comment Mokhschild is really brilliant.
          I had an imported document where the original numbers had the format "1 000". After the import, these numbers had been automatically converted to text : "1 000". But impossible to remove the "space" in the middle. Which means imossible to convert them back to numbers !!! This is indeed brilliant because it is really simple : copy the "invisible" character (the pseudo-space) and paste it in the search !!! Thank you so much !

        2. Microsoft Docs contributor guide overview - Contributor Guide | Microsoft Docs

  8. Is there a formula or a function that can be placed into a cell in excel that will automatically restrict space entery, at the time that data is being entered, to 1 space between characters.
    Also how would I lock the formulas in cells, so they cannot be changed but allow data entery into the cell.
    Many thanks

    1. You can lock cells easily enough. Just right click the cell (or group of cells) and select format cells. Go to the protection tab and check the locked box. Note by default ALL cells are typically set. So really you have to unlock the cells you want to be able to change.
      After you've made your sections go to file -> protect workbook. You can select a workbook, sheet whatever... once you've done that the cells will not be changeable unless you go back and undo the protection again via file -> protect. Optionally you can put a password on the protection to keep users out of modifications.

  9. Just wanted to give my thanks. That SUBSTITUTE command just saved 2 days of data inputting in a 2000 rows spreadsheet.

  10. Thank you buddy you saved the day :)

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

  12. Thank you.

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

    1. Hello, Girish,

      Please note that the Ablebits tab will appear in your Excel Ribbon once you install our software. If you want to use our Trim Spaces add-in to remove extra spaces from your worksheet, feel free to install a fully functional trial version of Ablebits Ultimate Suite for Excel and see if the tools work for you.

  14. thank you!

  15. Thanks buddy

  16. Dear All,

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

    1. Dear Ahamed,
      Thank you for your question.

      Please note that you can remove extra spaces using the Trim formula. However, our Trim Spaces add-in can help you solve this task faster and easier. It is available as a part of our Text Toolkit that contains 8 useful add-ins to manage text data in Excel (remove extra spaces, substrings and non-printable characters, change case, split cells, etc.).

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

  18. remove space between numbers did not work for me!

    1. Worked for Me.

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

  20. I want revmove space 53X 101

  21. good one

    Thank you

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

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

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

  25. Perfect... its really wonderful

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

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

  28. Excellent

  29. 1000
    5000
    2000

    how can i remove the space, plz help

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

    thanks

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

  32. Its very healpfull

  33. Very helpful. This saved so much time.

  34. Very helpful.
    Thanks a lot

  35. Thanks a lot. Very useful.

  36. VERY HELPFULL

  37. Great job , it really worked for me
    Thanks

  38. dear Sir,

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

    how to use trim formula here

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

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

  41. thanks very much for a good work done

  42. Precisely I got what I want

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

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

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

    It worked.......

  45. 04 100 950105 - this is the original

    04100950105 - i want to make it like this

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

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

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

  47. tnx a lot

  48. thanks a lot for your suggestion .

  49. this was wonderful. it saved me hundreds of working minutes

  50. Thanks a lot

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 :)