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 3. Total comments: 265

  1. when i remove a space in a phone number e.g 087 9876543 i am getting 879876543- the space is gone but so is the initial 0. any fix

    1. Hello!
      You didn't say how you removed the space from the number. If you use the SUBSTITUTE function (see example above), the result will be the text "0879876543".
      The same result is obtained using Ultimate Suite for Excel: Ablebits Data - Remove Characters.
      This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

  2. how to add a space in this and make it a proper statement ?

    Can anybody suggest?

  3. How can I trim spaces from "selected cells" (not an entire column) - I have a spreadsheet where I only want to trim spaces from selected cells, within one column, but not all. Is there a way of selecting the cells, by using CTRL + select, etc., without removing spaces from non-selected cells?

    1. Hello!
      We have a tool that can solve your task in a couple of clicks.
      The way you are talking about is possible if you use Ablebits Data - Trim Spaces.

      This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

      Your way to remove spaces is not possible with standard Excel tools. If you are using the TRIM function that only works on one cell, then apply it only to the desired cells. If you use "Find and Replace", then with the button Find Next, skip individual cells.

  4. how to remove space like
    A 25052

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      The formula below will do the trick for you to remove space:

      =SUBSTITUTE(A1," ","",1)

  5. best solution i have found so far

    =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))

    1. This really works. Thanks!

    2. Great, Thanks. its a perfect solution.

    3. Thanks.

    4. That's the one!

      Thank you!

  6. I have this information in one cell (column A)
    VALLEJO - A52
    I need to delete - A52 from column A and copy the A52 into column B.
    So this is the final product
    A52 (Column B) Vallejo (Column A)

  7. Lookout for non breaking spaces!
    It looks like a normal space but it is not.
    Find and replace using a normal space character does not work!
    One has to find #(00A0) and replace it!.

  8. Any idea how to remove a space after the number, for example 150.44__
    The __ is where my space is
    Thank you

  9. Hey
    How do you remove spaces from a cell that contains numerical data quickly when all of the cells have different amounts of spaces before them.

    For example “ 550”, “ 300”, “ 6000000”

    1. Hello!
      I hope you have studied the recommendations in the above tutorial. Please specify what formula you used and what problem or error occurred.

  10. FT-20-03-015557
    FT-20-03-015568
    FT-20-03-016249
    FT-19-12-005014
    FT-20-03-016227
    FT-19-12-004990
    FT-20-03-016236
    I choosed the formula of "Replace all", but the result is showing that, excel can't do it in protected data, why ?

  11. I had a strange situation with excel spaces. There was a range of cells with numbers in them. In all the cells there were several spaces before the numbers. Some cells had 27 paces before the number and others had more. I tried trim and it did not work. I also tried find and replace, but it did not work as find and replace can replace only a single space at a time, but here we had multiple spaces. So I copied the range of cells into a blank word document and did the find and replace to find all spaces and replace with with blanks. All the spaces were removed and I copied back the range of cells to excel, without any spaces of course. Hope this helps.

    1. This worked! thanks for sharing

    2. @ Charles
      I had same issue with various number of spaces in each cell just after the core numbers data. The Word tip works well. Many thanks for sharing this.

      1. I have same issue, i have already using TRIM, SUBSTITUTE, CHAR(160), CHAR(32), copy to document/notepad and did the find and replace to find all spaces and replace with with blanks but all not working. Something I missed?.

        2 232 239.38
        922 766.08
        79 331.90
        297 858.47
        166 524.86
        445 863.54
        212 086.95
        107 807.58
        50 269.83
        377 522.56
        219 221.27
        158 301.29
        1 212 093.09
        920 945.35
        113 798.98

  12. Thank You Very Much

  13. Removing spaces using find and replace option and substitute function can put up all the strings together.

  14. How do I remove the line separator in a bulk mobile leads? Example is below

    206-200-0000
    206-200-0001

    2062000000
    2062000001

    1. Select the columns and press ctrl+H
      Find=(-)
      Replace (keep Black) then Replace All

      1. If Space is in the start
        Like eg. Number in the cell start with space then what " 123456789"

        1. Hello!
          I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?

  15. Thank you so much - the trim formula has saved me so much time :)

  16. all of my entries are like "4.83 (19.12)" and i want to delete the portion in percentages because i want to find the minimum value of the first number for a large number of cells. does anyone know how to do this? I have tried above methods with no luck

  17. How to remove space after all the digits, as all above method tried but purpose not fulfilled.

  18. lovely..... May Allah accept this as a deed and give us guidance to the righteous path.
    EXCELLENT

    1. May Allah praise us

    2. Yes

  19. Thanks for sharing the skills. It is really helpful

  20. THANKS FOR THE SOLUTION
    FIND & REPLACE IS BEST WAY

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

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

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

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

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

  26. 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," ","")

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

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

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

  30. Thank you buddy you saved the day :)

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

  32. Thank you.

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

  34. thank you!

  35. Thanks buddy

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

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

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

    1. Worked for Me.

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

  40. I want revmove space 53X 101

  41. good one

    Thank you

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

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

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

  45. Perfect... its really wonderful

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

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

  48. Excellent

  49. 1000
    5000
    2000

    how can i remove the space, plz help

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

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