Tip: Quickly remove spaces from the beginning and end of your data

Save 5 minutes a day by using ASAP Utilities to quickly remove leading and trailing spaces from your data.

“Easily trim all leading and trailing spaces”

Sometimes your data contains leading or trailing spaces. Someone may have accidentally added extra space characters, or imported text data from external sources may contain extra spaces.

Leading and/or trailing spaces can cause unexpected results when you sort, filter, or use formulas.
But it is easy to fix, just select the cells and use:
ASAP Utilities » Text » Delete leading and trailing spaces

Delete leading and trailing spaces

This will:

  1. Remove leading and trailing spaces.
  2. Also handle non breaking spaces (this so called character, char 160, is commonly used in Web pages as the HTML entity,  ).
  3. Empty cells that only contain one or more spaces.
  4. Turn all empty cells into truly empty cells that Excel recognizes.

Do you recognize any of these situations?

  • I need to delete a leading space from multiple cells, 800 or more. The leading space is in front of text that is often more than one word so I can’t just do a find and replace on the spaces.
  • How can I remove spaces to the left and right of a text string only, and preserve the spaces inside the text string? Excel’s =TRIM() function reduces the spaces inside the text to single spaces.
  • I have some cells containing imported text with a lot of leading spaces. I try to get rid of them by using the =TRIM() function on those cells, but it doesn’t work.
  • My workbook contains data imported from another application and unfortunately many of these cells have padded leading blanks added.
    I need to remove these, but 4000+ cells individually will take hours.
  • I have difficulty sorting my data due to leading spaces. Manually removing these spaces takes hours. Is there some magic trick for this?
  • Your =VLOOKUP() formulas can’t find any matching values because of unwanted spaces.
  • You were sent a list with addresses for a mailing but it looks bad with all extra spaces.
  • Cells that appear empty aren’t because they contain a space.
  • Excel doesn’t recognize some cells as being empty. That happens often with imported data.
  • You try to remove the spaces by using Excel’s =TRIM() function but that doesn’t remove the spaces. That can happen with so called “non-breaking” spaces. See also this article from Microsoft that describes how to remove these unwanted spaces.
  • Duplicate values aren’t removed because of leading and/or trailing spaces in some values.

Just select the cells and then choose the following tool in the Excel menu:
ASAP Utilities » Text » Delete leading and trailing spaces

This is often easier and faster than Microsoft’s advised method and using the =TRIM() function.

Bonus tips, also interesting

How much time will it save?

It’s guaranteed that you’ll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.

Download

In case you don’t have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page

Please wait...

Subscribe to ExcelMOOC posts

Want to be notified when our Excel article is published? Enter your email address and name below to know all new Excel features and cool tips