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) Excel trim formula delete spaces
  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, the Trim Spaces tool 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, and line breaks.

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



Table of contents

265 comments

  1. Awesome!!

  2. I have copied a table from outlook & there having some junk characters which are looks like space but not space actually.

    All ways for clean the value column are done, number format also applied but still calculation not possible.

    can you please support for the problem.

  3. How can I get rid of leading and trailing spaces in a cell with multiple lines of text? The TRIM function works for a cell with just one line of text

  4. Thank you so much!! Your tips saved me time from having to update, and I count...5636 rows of data!!!!! That would have taken me days to update.

  5. SO much Thanks with Respect and Gratitude

  6. Thanks a lot. it was a big help.

  7. You are a god whoever made this page; thank you so much you saved me so much time with space trimming

  8. I am looking for some help. I have a column of numbers with a ' before each number, however, there are a bunch of them that have what looks like a space and it is not tight against the left edge. I have tried the Trim function, but that does nor work. I am not sure as to how to fix this.
    Thank you

  9. I have an excel sheet which is received from my bank but amount column having spaces in start of the numbers i put the formula of SUBTITUTE and TRIM but cannot sum of total value of amount by selecting of column.

  10. Thanks, 3rd option im use

  11. Thank you Alexander, very helpful.

  12. How to add VLOOKUP in google spreadsheet

  13. 60110 - Payroll Expense, 60120 - Payroll Taxes, 60130 - Overtime Wages, 60140 - Vehicle Allowances, 60150 - Medical Insurance, 60160 - Other Fringe Benefits, 60170 - Retirement Contribution, 60180 - Savings Contribution, 60190 - Vacation Expense

    Above is the data that I am looking at. I just want to remove Payroll Expense out of it but keep everything else. How do I do that in excel?

  14. Thanks for sharing such a useful information!

  15. HOW CAN I REMOVE SPACE AFTER TEXT i,e "ADAPTER,3/8NPTX1/4NPT
    "

  16. That's great, it works. Thanks

  17. Thank you for the great tips!

  18. "UPI/1336 2071/P
    ayment from Ph/702027
    2314@ybl/ TJ"

    I have such issue. I have done copy paste fron excel cell that was only in one cell. but as I copied and paste here it has come into three lines. What shoul I do???

  19. Hi,

    If a have a column with Surnames, a space and just the initial of their First name.
    (i.e. as show below.)
    How do i remove the space and the letter for the whole list? Or do i have to do it individually?

    Bloggs J
    Thomas M
    William T
    Jones A

  20. dear sir,
    i need a formula for space between two text.

    eg: Tableofcontents = Table of contents.

      • Hi, i have copied a bunch of data from a table in a website.
        however, excel does not recognize this data as numbers, but rather as text (it also automatically sorts the text on the left side of the cell). changing the cell to 'general' or 'number' does not do anything. i can not make graphs off this data since excel does not recognize it as data.

        I just figured out this is because all the datapoints have a space behind their value, so for example: "3,7 " instead of "3,7". when i remove this extra space, the datapoint alligns on the right side of the cell and is recognized as a number again.

        however, I can not seem to remove the extra spaces using the methods described on this website, any ideas?

          • Hello,

            I have the same problem as S. The data was copied from a website and the numbers copied as text as: " 365,65" with extra space added in front of the numbers but the extra spaces cannot be removed with any of the suggestions above (neither trim nor replace seem to work). Any ideas how to remove them another way? There's way too many numbers to do it manually.

            Thank you.

            Kind regards.

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