Sep 14 2007

Excel Trim Formula

How many times have you tried to import a file into Excel, only to have extra blanks all over the place in the document?  Well that’s where the Trim Formula can come into play.  The Trim formula is used to clean up and remove all the leading and trailing blanks as well as all but one blank in-between text that you may have in a list of cells.  So now instead of going into each cell one-by-one, there is one formula to do all the work for you.  Let’s take a look at the formula first (it’s a short one):

=Trim(text)

This formula is very easy to use but can make you job a whole lot easier. Let’s take a look at some of the examples now.

Examples

Example 1: =trim(”  Bird  “), will return “Bird”

Example 2: =trim(”  First       Second  Third     “), will return “First Second Third”

Trim Formula

Example 3: =trim(A2), will return “Mark Smith”

Example 4: =trim(A5), will return “Gene William Wilder”

Hopefully the few examples I listed above help you to understand how to use this formula and will save you some time the next time nothing seems to line up correctly.  If you have any other questions please leave a comment below or contact me using the contact form.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

8 Comments on this post

Trackbacks

  1. Sarika Khurana said:

    Hi there,

    Is it possible to have a macro which auto trim all the cell within the selected cells.

    thanks

    November 28th, 2007 at 6:14 am
  2. Eliza said:

    Hi there,

    Once you do this formula, how can you delete the orginial cell? ie once I delete the orginal cell then the new one is deleted as well.

    Regards,

    Eliza

    November 9th, 2009 at 6:18 pm
  3. john said:

    Hi Eliza,

    You will have to freeze the cell values after you do this if you plan on deleting the original cells. If you do a copy and paste special with values only selected, it will effectively freeze the cells.

    Then you can either manually delete the original data, or right a macro to do it for you.

    November 9th, 2009 at 6:42 pm
  4. Terrence Shorette said:

    Do you mind if I quote a couple of your articles or blog posts as long as I provide credit and sources back to your blog: http://excelhints.com/2007/09/14/excel-trim-formula. I’m going to aslo make certain to give you the appropriate anchortext hyperlink using your webpage title: Excel Trim Formula. Please make sure to let me know if this is okay with you. Many thanks

    July 17th, 2011 at 2:23 am
  5. john said:

    Sure Terrence.

    July 18th, 2011 at 9:35 am
  6. Lee Chiew Yin said:

    can i hide the original date after the formula

    June 15th, 2012 at 12:55 am
  7. Lee Chiew Yin said:

    sorry,should be data

    June 15th, 2012 at 12:55 am
  8. Bob said:

    How do I copy a workbook containing 6 worksheets?
    Evertime I try, the new copy contains many unwanted changes within the formuals of each worksheets.

    Thanks Bob

    August 27th, 2012 at 10:22 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives