Remove Extra Spaces from Excel Data

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

Fred Pryor Seminars_Excel Formula Remove Spaces_1

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_2

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_3

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_4

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_5

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_6

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_7

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_8

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.

Fred Pryor Seminars_Excel Formula Remove Spaces_9

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!

Fred Pryor Seminars_Excel Formula Remove Spaces_10

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?

 

Local Seminars Related to this Topic:

Related Advanced Excel Articles

Categories

Monthly Archives

39 Comments

  • CC says:

    Thanks for the detailed explaination

  • Hussain says:

    Thank you so much.This was really helpful

  • Ferofax says:

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

  • Moe says:

    Thank you for this!

  • Dave says:

    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?

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Anis says:

    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.

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Ehsan says:

    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.

    • Excel Tips and Tricks from Pryor.com says:

      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:
      Paste Value

      • Nick says:

        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

        • Excel Tips and Tricks from Pryor.com says:

          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.

  • Mark says:

    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

    • Excel Tips and Tricks from Pryor.com says:

      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:
      reply3-1
      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”.

  • Ray says:

    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.

  • Frans Sabbat says:

    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?

    • Excel Tips and Tricks from Pryor.com says:

      The “show formulas in cells” option could have been tripped. There are two ways to fix this problem.

      1. You can use the keyboard shortcut: CTRL + `
      2. The keyboard shortcut allows you to toggle this options on and off and is typically how this feature is tripped.

      3. You can go to file, options, advanced and scroll down until you find the “show formulas in cells instead of their calculated results”
      4. 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

  • Pat says:

    I am trying to paste several words from outlook into a cell but cannot eliminate spaces and blank lines.

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • DEDEN SAEFUL ASHARI says:

    Hello,

    How to clear space on last text
    example :
    Jon Smith”space” –> how to clear space on last text .?

    pls answer

    Thank you

    • Excel Tips and Tricks from Pryor.com says:

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

  • Tracey says:

    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

    • Excel Tips and Tricks from Pryor.com says:

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

  • Tracey says:

    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 🙂

    • Excel Tips and Tricks from Pryor.com says:

      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.

      1. If it lets you remove the space and cut off seconds – the column isn’t formatted for time/date.
        • a. If the column were formatted for time/date, removing the extra space won’t “stay”. The space will be restored when you leave the cell.
        • b. Removing the seconds shouldn’t work either unless you’ve removed them from the formatting also (seconds would just default to zero and remain in the cell).
        • c. If seconds were removed from formatting then the wrap should be correct.
      2. It sounds like the column width hasn’t been adjusted either.
        • a. Have you tried changing the column width or is there a reason that shouldn’t be done?

      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.

  • Karyn says:

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

  • Amber says:

    This was an excellent clear how-to guide, and as a bit of an excel virgin I am grateful for the help!

  • J.F. says:

    Thank you for sharing these tips! This is truly appreciated!

  • Carrie Padron says:

    The heavens opened and shined their glorious light onto these tips. Thank you greatly!

  • Anup Thomas says:

    Thank you .. it was very helpful

  • Marty Moore says:

    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.

  • Joseph says:

    Thanks! Btw this blog post is even better than the Microsoft help page

  • Kyle says:

    “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?

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Raphael says:

    Thank you very much for this well detailed tutorial. Helped me!

  • Gail says:

    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.

  • Steven Volpe says:

    This was perfect! It was clearly written and easy to follow…and it worked!!!
    Thank You!!!

  • JulsB says:

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

Leave a Reply

Your email address will not be published. Required fields are marked *