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 cant be summed up, and again extra blanks are the ones to blame.
Working with data in Excel often leads to extra spaces in cells that create issues down the road. These extra spaces can come from badly copied data or incorrect pasting. Whatever the cause, extra spaces, newlines, and other whitespace characters can make your data messy and harder to work with.
In this article, we’ll explore 5 straightforward methods to clean up extra whitespace in Excel making your spreadsheets easier to analyze and maintain.
Understanding Excel’s Whitespace Issues
Before diving into solutions let’s briefly cover what kinds of whitespace problems crop up in Excel. The main culprits are
-
Leading and trailing spaces: Extra whitespace before or after text in a cell. This often happens when pasting text.
-
Extra spaces between words: More than one space between words in a cell. Copied text can introduce these accidentally.
-
Newlines: Manually pressing Alt+Enter in a cell creates newlines within the text.
-
Non-printing characters: Certain special whitespace characters that don’t display but cause issues.
Too much whitespace makes your data untidy and creates headaches whether you’re trying to analyze, sort, or lookup information in your sheets.
Fortunately, Excel provides easy ways to strip out unnecessary whitespace. Let’s look at 5 of them, from built-in functions to Find & Replace.
1. Use the TRIM Function
Excel’s TRIM function quickly removes leading, trailing, and extra spaces between words in one go.
The TRIM formula looks like:
=TRIM(A2)Where A2 contains the text you want to clean up.
TRIM removes all whitespace at the start and end of the cell’s text. It also deletes any double spaces between words, while keeping single spaces intact.
This makes TRIM ideal for tidying up full names, addresses, or any text where spaces between words are needed.
2. Combine TRIM and CLEAN
The TRIM function won’t remove newlines or certain non-printing characters. For that, we can use the CLEAN function together with TRIM.
CLEAN deletes all non-printing characters from a cell. Using CLEAN and TRIM together removes spaces, newlines, and other special characters:
=TRIM(CLEAN(A2)) The CLEAN function first strips out newlines and non-printing characters from the cell. TRIM then removes any leftover extra spaces.
This combo covers all the common whitespace offenders in one go!
3. Remove All Spaces with SUBSTITUTE
When you need to delete all spaces from cells, TRIM isn’t enough since it keeps single spaces between words.
For that, the SUBSTITUTE function comes to the rescue. SUBSTITUTE replaces specific text in a cell with something else.
To remove all spaces, we can replace them with empty text:
=SUBSTITUTE(A2," ","")This substitutes every space character with nothing, effectively deleting them entirely.
To take it further, we can use SUBSTITUTE twice to remove newlines as well:
=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),"")," ","") The CHAR(10) code removes newlines. Doing a second SUBSTITUTE with a regular space deletes those too.
4. Use SUBSTITUTE with CLEAN
We can make an even more powerful formula by combining CLEAN and SUBSTITUTE:
=CLEAN(SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(10),""))This does the following:
- Remove newlines with SUBSTITUTE and CHAR(10)
- Remove regular spaces with SUBSTITUTE
- Delete any remaining non-printing characters using CLEAN
The end result is text completely stripped of all whitespace and newlines in one step!
5. Find & Replace Whitespace
Lastly, Excel’s Find & Replace tool provides a quick way to remove spaces and newlines.
To use Find & Replace:
- Select the cells to operate on.
- Press Ctrl+H to open the Find & Replace dialog.
- Enter a space in the Find field and leave Replace empty.
- Click Replace All.
- To also remove newlines, put CHAR(10) in Find and repeat Replace All.
This manually replaces all spaces and newlines with nothing.
The only downside is you have to run Find & Replace multiple times to remove different whitespace characters.
Putting it All Together
- TRIM – Delete leading, trailing, and extra spaces between words.
- CLEAN + TRIM – Remove spaces, newlines, and non-printing characters.
- SUBSTITUTE – Delete all regular spaces.
- SUBSTITUTE + CLEAN – Strip all spaces and non-printing characters.
- FIND & REPLACE – Manual space and newline removal.
No more pulling your hair out trying to manually delete wonky whitespace in Excel! Next time you paste messy data, use one of these tips to clean it up in seconds. Removing unwanted spaces, newlines, and odd characters makes your spreadsheets easier to work with and analyze.
Have you encountered tricky whitespace issues in Excel before? How did you overcome them? Share your Excel cleaning tips and tricks in the comments!
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: 
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:
- Add the helper column to the end of your data. You can name it “Trim”.
- In the first cell of the helper column (C2), enter the formula to trim excess spaces
=TRIM(A2)
- 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.
- 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
. Then just press V. 
- Remove the helper column.
Thats it! We deleted all excess blanks with the help of the formula trim(). Unfortunately, its a bit time-consuming, especially if your spreadsheet is rather big.
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 doesnt 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.
- Select one or several columns with the data to delete spaces between words.
- Press Ctrl + H to get the “Find and Replace” dialog box.
- Press the Space bar twice in the Find What field and once in Replace With
- Click on the “Replace all” button, and then press Ok to close Excel confirmation dialog.
- Repeat step 4 until you see the message “We couldnt 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:
- Download and install a trial version of Ultimate Suite for Excel.
- 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.
- Go to Ablebits Data tab and click on the Trim Spaces icon.
- The add-ins 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.
Isnt 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. 
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.
How to Remove Spaces in Excel (Two Useful Ways)
How do I remove spaces from a cell in Excel?
Select the cell or cells you want to remove spaces from, enter the formula =TRIM (CONCATENATE (cell1,cell2,cell3,…)), where “cell1”, “cell2”, “cell3”, etc. are the cell addresses of the cells you want to remove spaces from, and press Enter to apply the formula.
How to remove blank spaces in Excel?
Whenever you want to remove blank spaces in your Excel sheets, perform these 4 quick steps: Select the cells (range, entire column or row) where you want to delete extra spaces. Click the Trim button. Done! All extra spaces are deleted in a single click. This is how you can quickly remove spaces in Excel cells.
How to remove leading and trailing spaces in Excel?
Power Query is Excel’s tool for cleaning dirty data. However, it is as easy as using the methods above. To remove the leading and trailing spaces, select the column to be cleaned and click Transform > Format (dropdown) > Trim from the Ribbon. The leading and trailing spaces are removed instantly.
How do I remove a space from a data set?
On the Replace tab, place one blank space in the Find what field. Make sure there is nothing in the Replace with field. Hitting Replace All (keyboard shortcut: Alt + A) will remove any instances of a space in the data set that you selected. Although this method is really quick and easy, it’s only useful for data where you want ALL spaces removed.
