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

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.

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

You may also be interested in

Category: Excel Tips

Table of contents

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

  1. One Guy says:

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

  2. Suzanne Walker says:

    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

    • cbyjpr says:

      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.

  3. Bet says:

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

    • Mokhschild says:

      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

      • Tim says:

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

        • de japonais gratuits says:

          Microsoft Docs contributor guide overview - Contributor Guide | Microsoft Docs

        • Kar says:

          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 !

      • Ilhame says:

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

      • Jan says:

        Super, thanks!

      • George says:

        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.

      • Kevin A. says:

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

      • Mire says:

        You saved meeeee!!!!! many thanks

      • Clinton Hommel says:

        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!

      • Clinton Hommel says:

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

      • Max says:

        Nothing else was working - this did! Thankyou!

      • VIMAL says:

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

  4. Venkataratnam says:

    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

  5. Marius says:

    625 749.41
    2 260 216.41
    570 246.96
    308 608.25
    2 084.46
    42 840.00
    206 271.99
    772 026.85
    26 735.61
    1 493 364.26
    193 150.50

  6. Mar says:

    how do I convert these to numbers

    625 749.41
    2 260 216.41
    570 246.96
    308 608.25
    2 084.46
    42 840.00
    206 271.99
    772 026.85
    26 735.61
    1 493 364.26
    193 150.50

  7. Steven P Brown says:

    This article saved me alot of time and frustration.

    • Oytisa Onato says:

      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'

  8. aditya says:

    2415927226.30 how to remove top space??

  9. Santy Tanwar says:

    37820-55C -D250M1

    How can i remove space b/w 55c to hyphen with a formula
    pls suggest any formula below are more code
    37820-55C -D250M1
    37820-55C -D550M1
    37820-55C -D850M1
    37820-55C -N040M1
    37820-55C -N210M1
    37820-55C -N550M1
    37820-55C -N650M1
    37820-55C -N710M1
    37820-5V6 -D810M1
    37820-5XJ -E110M1
    37820-5XJ -E210M1
    37820-5XJ -E511M1
    37820-5XJ -N010M1
    37820-5XJ -N110M1
    37820-5XJ -N510M1
    37820-5XJ -N610M1
    Thanks

  10. Femi says:

    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

  11. shonam says:

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

    • Huma says:

      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.

  12. Abder-Rahman says:

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

  13. VYSHAK E says:

    5703.10.10 How to i convert word foramt (Any Fromulas??)

  14. Alex Yepes says:

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

  15. VINOD says:

    THANKS FOR THE SOLUTION
    FIND & REPLACE IS BEST WAY

  16. Pappu says:

    Thanks for sharing the skills. It is really helpful

  17. Muhammad Adnan says:

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

  18. M Khalid says:

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

  19. sean says:

    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

  20. Rita says:

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

  21. Jimmie says:

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

    206-200-0000
    206-200-0001

    2062000000
    2062000001

  22. Medicaltipes.Com says:

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

  23. Elfrid says:

    Thank You Very Much

  24. Charles says:

    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.

  25. NASEEF says:

    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 ?

  26. SK says:

    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”

  27. Lisa T says:

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

  28. Johann says:

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

  29. Jeanette says:

    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)

  30. shy says:

    best solution i have found so far

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

  31. pradeep avasthi says:

    how to remove space like
    A 25052

  32. Ciaran says:

    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?

  33. amit says:

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

    Can anybody suggest?

  34. Ger says:

    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

  35. Saurin Shah says:

    Hello !
    Can anyone suggest me how to convert my below data from different rows to sequential rows without any space/blank row. I want to know to how can we remove multiple rows in a single command/formula.
    RadioSrNumber

    EIC3820915MHZ-000108
    RAD915MHz-000108

    EIC3820915MHZ-000109
    RAD915MHz-000109

    EIC3820915MHZ-000454
    RAD915MHz-000454

    EIC3820915MHZ-000499
    RAD915MHz-000499

    EIC3820915MHZ-000519
    RAD915MHz-000519

    EIC3820915MHZ-001149
    RAD915MHz-001149

    EIC3820915MHZ-001457
    RAD915MHz-001457

    I wan to covert this above mentioned data into data as per below format

    EIC3820915MHZ-000108
    EIC3820915MHZ-000109
    EIC3820915MHZ-000454
    EIC3820915MHZ-000499
    EIC3820915MHZ-000519
    EIC3820915MHZ-001149
    EIC3820915MHZ-001457

    • Hello!
      You cannot delete rows using an Excel formula. We have a tool that can solve your task in a couple of clicks - Ablebits Tool - Select by Value. After that, you can delete the rows with the selected cells. Use menu Home - Cells - Delete Sheet Rows.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      If you have any other questions, please don’t hesitate to ask.

  36. Khushal singh says:

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

  37. Phil says:

    The title of the article "3 ways to remove spaces between words / numbers in Excel cell". You then go on to explain How to remove EXTRA spaces. For those of us looking to do what your title says, remove (ALL) white spaces the article is useless.

    The way to do this, and to answer the question about removing the "-" character, is to use the substitute command. Assuming the original text is in cell B3, the command would be, =substitute(B3, " ", "") .

    Any character put in as the second argument will be removed. For exmample, the get rid of dashes the command is, =substitute(B3, "-", "").
    To get rid of commas the command is, =substitute(B3, "," , "")

  38. SREEDHUL A K says:

    Item Base price Disc @ 16.75% Net price after Disc ~CGS for January/ Pair Royalty @1% SBU OH Cost per unit for SBU GP GP% NP NP% Net profit/ (Loss) for production taken in DEc and Jan(20-21)
    1. 8143 MRN 5X9 152 25.46 126.54 123.78 1.27 19.00 144.05 1.49 1% -16.24 -13% -80,810
    2. DG9800 N.BLU 6X10 189.00 31.66 157.34 153.88 1.57 19.00 174.45 1.89 1% -15.54 -10% -1,97,388
    3. DG9804 BLK 6X10 183.00 30.65 152.35 143.23 1.52 19.00 163.75 7.59 5% -9.88 -6% -1,08,223
    4. DL3011 BLK 5X9 140.00 23.45 116.55 98.42 1.17 19.00 118.58 16.97 15% -0.87 -1% -11,338
    5. DL3411 N.BLU 5X9 128.00 21.44 106.56 92.14 1.07 19.00 112.21 13.35 13% -4.58 -4% -75,717
    6. DL3703 BLU 5X9 164.00 27.47 136.53 108.03 1.37 19.00 128.40 27.13 20% 9.50 7% 68,315

    Please specify the how to remove the space

  39. Gayle Wallin says:

    Thank you

  40. Gayle Wallin says:

    Thank you 4 your tips

  41. Perumal says:

    how to remove space after the number ie 123

  42. yinka says:

    kindly avail us the sample to practise

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