Does this sound familiar? You are searching for data that you know you’ve entered, but a query for Joe Smith simply doesn’t return any results. Or, how about – you are trying to add up two columns of numbers, but you keep getting errors.
Often, these issues are caused by extra spaces hiding before, after or between the text and numbers in your data. Especially when information has been copy/pasted into a spreadsheet, extra spaces can come along for the ride and lurk unnoticed until something goes wrong.
Excel offers several ways to help you remove spaces and clean up your data, but you will want to choose the right technique for the type of data you are working with. The following tips will describe two methods using functions and formulas to remove unwanted spaces from your data.
These steps will apply to Excel 2007-2013. Images were taken using Excel 2013 on the Windows 7 OS.
Option 1
Use the Trim Function
The TRIM function removes all spaces from text except a single space between words. Use this function when you have text data that contains hidden spaces in the cell. In our example, you’ll see that there are multiple “Joe Smiths” in our data. At a glance, several of them even look the same. However, the # of Characters column reveals that there are hidden spaces buried in the text.
Step 1
To use the TRIM function, you will need to create a temporary, or helper, column for the corrected data at the end of your spreadsheet. For the purposes of this demonstration, we’ve called the column “Trim.”
To follow using our example above, download Excel Remove Spaces Formula Template
Step 2
Click in the first cell of the Trim column. On the Formulas tab, click the Text dropdown menu in the Functions group and select TRIM.
Step 3
After the dialog box appears, click on the cell that contains the text you would like to remove spaces from to complete the function. Click OK.
Step 4
Copy the function to the remaining cells in the column to apply the function to the rest of the Name cells. You can see that the TRIM function cleaned up the extra spaces and the character counts are now all the same except for the “JoeSmith” that did not have any spaces between the first and last name. Note that TRIM will not ADD spaces.
Step 5
Now you just need to replace your original data with your clean data. To do this, select all of the cells in the Trim column, then hit CTRL+C to copy the cells to the clipboard.
Then, select the first cell in the Name column and right-click. Select Paste Values under the Paste Options heading. Now you can Delete the Trim helper column and your Name column is clean.
Option 2
Use a Formula to Remove All Spaces
If you need to prepare your data for import into a database (such as MS Access) or have a combination of number and text data that simply needs ALL spaces removed, you can use a formula instead of the TRIM function to remove ALL blanks in a cell.
Step 1
Create a temporary, or helper, column for the corrected data at the end of your spreadsheet like you did for the TRIM function. In this example the column is called No Spaces.
Step 2
Click in the first cell of the Trim column. On the Formulas tab, click the Text dropdown menu in the Functions group and select SUBSTITUTE.
Step 3
When the dialog box appears:
- Click on the first cell in the column that you need changed
- In the “Old_text” field, enter the character you want replaced separated by quotes. To remove spaces this would then be ” “.
- In the “New_text” field, enter the character you want to appear instead. Since we want NO characters to replace the space, type “”.
Step 4
Click OK to apply the formula to the cell.
Step 5
Copy the formula to the remaining cells in the helper column. Then copy the data to the data column as you did before using CTRL-C and Paste Options.
Bonus Hint!
You can also remove spaces using the Find/Replace feature in Excel. Click CTRL+F to open the Find dialog box, then click the Replace tab. Enter one space ” ” in the Find what: field and leave the Replace with: field empty to remove all spaces.
Warning: This technique will search for and replace ALL spaces in the entire sheet or selection area. Use carefully so that you don’t remove spaces you still want or need!
With these tips, you can clean up data entered from multiple sources and ensure that it is consistent and less prone to unintended duplication. Use these tips to remove spaces when you want to:
- Find unintended duplicates in your data
- Prepare someone else’s data before you import it into your own spreadsheets
- Prepare your data for export into a database
When do you find the need to find and remove unwanted spaces?
39 Comments
Thanks for the detailed explaination
Thank you so much.This was really helpful
Holy guacamole this was enlightening to say the least!
I process a lot of Excel files, and a lot of the people that give me said files like to “format” their text in the cell by adding TONS of spaces to push the next word down the line. I hate, hate, HATE that when I see it, because more often than not, I use a template that has different cell widths, and BOOM, their formatting suddenly looks like platforms for Super Mario Bros.
THANK YOU THANK YOU THANK YOU !!!
(I left a few leading and extra whitespaces for you people to enjoy, lol. Cheers!)
Thank you for this!
Thank you. Tried to use this with a date that has an extra space at the beginning and an extra space b/w the year and time(Ex: 1 December 2015 08:18 AM). Problem is Excel requires that double space in order to convert “1 December 2015 08:18 AM” to “12/1/2015”. Is there a non-manual way to trim extra spaces, but not the double-space b/w the year and time?
Possibly try using the DATEVALUE() function and refer to the cell with the text
=DATEVALUE(A3)
This function will completely ignore spaces and return the serial date 42339
That’s an Excel date in its raw form.
All you need to do now is to format the cell as a date and choose which way you’d like it displayed.
if any email is copied from any url and pasted in excel with trailing spaces , how do you remove those spaces. I tried with Trim function but does not work.
Please help.
Another option would be to try the MID() or LEFT() functions which allow you to pull a specific number of characters from a text in another cell.
I have to trim values like 2.1.1.10 and with multiple spaces in the end. I applied trim function and then copied and pasted the cell to a new cell but it carries all spaces again.
If you copy and paste the Raw Data normally – the spaces will follow.
If you copy and paste the Function Result, be careful to Paste Values (not an ordinary Paste).
This will keep the TRIMmed spaces off.
This is the Paste Values option you should look for:
Does this work the same way with email addresses? I exported some emails from my php list to excel and they had spaces at the end. When I followed these instructions, I was unable to get the spaces stay off, even when using the paste values option. Any guidance will be greatly appreciated
The problem could be that there are “invisible” characters in the email addresses. There are dozens of “invisible” characters possible in ASCII (the character code most computer use to represent written letters). TRIM() will only affect genuine keyboard spaces (character 32). If these “spaces” are actually tabs or carriage returns, TRIM will have utterly no effect on them.
Hi
I tried all all the above but it doesn’t get rid of the double spaces when the double spaces look like the below in the formula bar:
Data Criticality 5 Break Fix
(Branch)
Ideally I need it to look like:
RBS Data Criticality 5 Break Fix (Branch)
Can you help?
Thanks
The most likely problem is just that the column is too narrow to display the entire text and the wrap property has been set. In this case, there isn’t any need to search or replace. If you right click over the cell in question and choose “FORMAT CELLS”. You’ll be presented with these options:
Go to the ALIGMENT tab. You can either uncheck “Wrap Text” which will cause the text to attempt to bleed over into the next column or you can leave “Wrap Text”.
Thank u very much for this, I have spent hours on the microsoft website trying to resolve this problem and not once did it tell me to use Substitute, which is what worked in the end.
Hi thanks you very much, its really cool and excellent formula to know. Why do I however get =SUBSTITUTE(A2,” “,””)and not the formula result that is displayed in the formula box window?
The “show formulas in cells” option could have been tripped. There are two ways to fix this problem.
The keyboard shortcut allows you to toggle this options on and off and is typically how this feature is tripped.
By placing a check mark in the box to the left of this option you will turn the formula display on and by removing the check mark you will turn the formula display off
I am trying to paste several words from outlook into a cell but cannot eliminate spaces and blank lines.
If you want to eliminate leading and trailing spaces, The function TRIM() will clear spaces off the back and the front of any text. Getting rid of unwanted “blank lines” could be handled by sorting the rows of text. If you select all the rows you pasted and do an ordinary SORT, from A-Z, all the blank lines will drift to the bottom and out of the main text. If there are unwanted spacing within the text itself, you might try Find/Replace from the HOME tab.
Hello,
How to clear space on last text
example :
Jon Smith”space” –> how to clear space on last text .?
pls answer
Thank you
The function TRIM() will clear spaces off the back and the front of any text. TRIM(“Jon Smith “) will result in “Jon Smith”. If “Jon Smith “ occurs in cell A1 then TRIM(A1) will also result in “Jon Smith”.
I just read this and found it helpful but not sure it will work for my issue. I have a LARGE spread sheet with a lot of rows – one column is a time/date column– and when I paste the data from the source to my spread sheet it puts an extra space between the date and time so the column only shows the date – I would like it to show the date on the top line and wrap the time to show underneath – format painter does not copy just the format but changes the value in the cell – (which I do not want it to do) I’ve tried to search for a solution but so far no luck — except to go line by line and MANUALLY take out the extra space which literally will take me hours….using the format button does not give the same result and I’ve even tried changing the format of how my clock displays to hope it might help – NOPE 🙁
I am going to rip out my hair soon 🙂
Thanks all
One scenario may be that your data is formatted as text. To fix this problem you select the column with date/time and right click, then choose “Format Cells”. A second dialog box will appear and you will want to click the “Alignment” tab. Then click the “Wrap text” control box.
Once that’s done, your rows will get tall enough for two lines of text and the time will shove down to the next row.
Thank you I will try that and see if it works 🙂 I appreciate the feedback – Have a good day
Tried it – didnt work 🙁 –here’s what I have – maybe that will help
04/04/2016 11:59:00 PM (WRONG)
if you remove the space (and the zero’s for the seconds (so the PM looks like I have below) it will wrap correctly….
03/31/2016 04:34PM (RIGHT)
I’m at a loss 🙂
We’re sorry to hear that didn’t work but just as perplexed. This usually means that there’s something active in your spreadsheet that has not been described in the problem.
If we were in a classroom, I’d check to be sure that column isn’t formatted for text because that’s the only way the effect described can be simulated in Excel.
If the time/date field has a “Custom” formatting – check it to be sure an extra space hasn’t been forced by the formatting itself.
Oh thank goodness! I have been trying to find a way to get rid of random spaces for a long time now and this is the first explanation that didn’t involve buying additional software or writing code.
Thank you for the very no-frills or sales solution!!!
This was an excellent clear how-to guide, and as a bit of an excel virgin I am grateful for the help!
Thank you for sharing these tips! This is truly appreciated!
The heavens opened and shined their glorious light onto these tips. Thank you greatly!
Thank you .. it was very helpful
Thank you, Thank YOU, THANK YOU! I rarely have the opportunity to use Excel but recently had to merge an Excel file with envelopes. I ended up going through 500 records for spaces hidden after the Title, First Name and/or Last Name before it would merge properly. Now I know what to do the next time that happens.
Thanks! Btw this blog post is even better than the Microsoft help page
“Copy the function to the remaining cells in the column to apply the function to the rest of the Name cells.”
Would this make every cell =TRIM(A2)? How do you get it to know to do A3, A4, etc. automatically?
One of the more important “Automatic” functionalities that Excel has always offered is called “Relative referencing”.
If Cell B2 contains the formula =TRIM(A2) and you copy that formula to B3, B4 and B5, Excel will assume that B3, B4 and B5 don’t want to all reference A1.
In fact, it will assume that B3 references A3, B4 references A4 and so on.
Give that a try. Copy B2 and paste it anywhere else in column B. The Row reference will “change” to the same row you paste to; automatically.
Thank you very much for this well detailed tutorial. Helped me!
I want you to know that this was THE MOST helpful tutorial I’ve found on this subject. Thank you so much for making it available. It really helped me out a lot.
This was perfect! It was clearly written and easy to follow…and it worked!!!
Thank You!!!
Thank you, thank you, thank you!! I could not get rid of the extra spaces no matter what I did. TRIM worked perfectly! Thank you so much!!! 🙂