The App Times

Excel 2010: How to Remove Extra Spaces in Cells

If you are in the habit of copying content from other spreadsheet 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 extra spaces in cells using the TRIM function.

How to Remove Extra Spaces in Cells

1. Place the cursor in the cell where you want the result to appear.

2. Type =TRIM(Cell Reference) and press Enter.

 How to Remove Extra Spaces in Cells

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

trim function for non breaking spaces

Now you can quickly remove those unwanted spaces in a jiffy and focus on getting your job done.

You can follow us on Twitter or join our Facebook fan page to get tutorials, updates and how-to articles on Microsoft, Google and Apple products.

Get the latest update in your inbox.

Subscribe to our mailing list and get tips, tutorials, and app reviews in your email inbox.

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and games on android-resources.net.

View all contributions by

    Comments on this entry are closed.

    • Vernessa Taylor February 22, 2011, 5:36 pm

      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!

    • Kim September 27, 2011, 3:29 am

      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?

      • somnath September 28, 2011, 12:41 pm

        Use find & replace
        find space & replace with blank

      • Christian October 28, 2011, 5:51 pm

        Kim you could use the function substitute to find the space and replace it with non space.

        for instance: =SUBSTITUTE(A1;” “;””)

    • Pat February 10, 2012, 11:17 pm

      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?

    • Doug February 12, 2012, 6:05 am

      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

    Get the latest updates
    in your inbox

    Subscribe to our mailing list and get tips, tutorials, and app reviews in your email inbox.