If you are in the habit of copying content from other spreadsheet or from a website to your Excel database, you might often be faced with the situation where the copied content display additional spaces either the beginning or end of a string. You may also find extra spaces within a string. In this Excel 2010 tutorial, you will learn how to remove spaces in excel (leading, trailing, or all ) under different circumstances.
How to Remove Spaces in Excel Using TRIM Function (Leading & Trailing Spaces)
1. Place the cursor in the cell where you want the result to appear.
2. Type =TRIM(Cell Reference) and press Enter.
The result is that text will now remove all leading and trailing spaces, as well as extra spaces in the middle of a string.
There may be times when the TRIM function does not work. For instance, when data is copied from a HTM page, the spaces in the cells are nonbreaking spaces with a decimal character code of 160 rather than a ASCII space decimal character code of 32. In such cases, you will have to use the formula
=TRIM(SUBSTITUTE(Cell Reference,CHAR(160),CHAR(32)))
Remove Spaces In Excel Between Words Using Find and Replace Functionality
If you need to remove extra spaces between words you can make use of the Find and Replace functionality. This will also remove leading and trailing spaces by one space but won’t completely remove them.
1. Select the range of cells where you want to remove the extra spaces.
2. Press Ctrl+H to display the Find and Replace dialog box.
3. In the Find what text place, click and hit the Space Bar twice.
4. In the Replace with text box, place the cursor and make sure the box is empty.
5. Then click Replace All.
Remove Space In Excel Between Numbers Using Find and Replace Functionality
If your workbook has numbers where there are spaces between the digits (thousands, millions, billions), Excel will read them as text rather than numbers and you won’t be able to perform any mathematical operations on them.
1. Select the range of cells where you want to remove spaces in excel workbook.
2. Press Ctrl+H to display the Find and Replace dialog box.
3. In the Find what text place, click and hit the Space Bar.
4. In the Replace with text box, place the cursor and make sure the box is empty.
5. Then click Replace All.
Remove Spaces In Excel Between Words
There may be times when you want to remove spaces in cells. In such situations you can use the formula =SUBSTITUTE(A1," ","")
Use a Third Party Tool to Remove Extra Spaces in Excel
If you routinely copy and paste data and need to clear extra spaces on a regular basis, using a third party tool would be more handy. One tool that I can recommend is Cell Cleaner ($19.95) from Ablebits. It has a 15 day free trial version for you to try before you decide to buy. Cell Cleaner can:
- Trim leading, trailing, and excess spaces between words.
- Change text case to Proper, UPPER, or lower.
- Convert numbers in text format to number format.
- Remove or replace unwanted and non-printing characters.
Hello Eddie,
Your Excel series is wonderful! I use both Excel and OpenOffice Calc (as well as Gnumeric in Linux) and your tips seem to work for Calc and Gnumeric, too. Thanks!
What about spaces in numbers? If I want to convert something like this:
703784 1394850 00330 7
to:
7037841394850003307
Is there a way to do it?
Use find & replace
find space & replace with blank
Kim you could use the function substitute to find the space and replace it with non space.
for instance: =SUBSTITUTE(A1;” “;””)
Thank you for the above answer on how to remove the excess spaces. Now, I’ve got a question about how to make that new column of space-less data into a useable column of data and not just of a formula. What I’d like to do is be able to replace the old column of data with this new column without all the spaces. Can it be done?
Good lesson, I never knew there was a “trim” function and I thought I knew excel well… This would have come in handy during many a project in my past… I’m seeing it a little too late, but will likely use it in the future