Tip: Get rid of unwanted spaces in your data

Save 5 minutes a day by using the "Text » Delete leading, trailing and excessive spaces" data cleanup tool:

Quickly remove all unnecessary spaces

With: ASAP Utilities » Text » Delete leading, trailing and excessive spaces
Remove all spaces from the beginning and end of your data and replace duplicated spaces between words with a single space.

A dataset with too many unwanted spaces

Delete leading, trailing and excessive spaces

Do you recognize this? It is easy to fix

  1. Your =VLOOKUP() formulas don't find matching data because of extra spaces in some values.
  2. You received a file with addresses for a mailing but it looks bad with all extra spaces.
  3. Cells that appear empty aren't because they contain a space. Believe it or not but there are people that press the space bar to "empty" a cell.
  4. Excel doesn't recognize some cells as being empty. That happens often with imported data.
  5. 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.
  6. Duplicate values aren't removed because of excess spaces in some values.

Solution

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

  1. Leading and trailing spaces will be removed.
  2. Correct irregular spacing, excess spaces between words will be replaced with a single space.
  3. Non breaking spaces are handled too.
  4. Cells with only spaces will be made empty.
  5. Empty cells will become truly empty cells that Excel recognizes.

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 much you use Excel, the amount of data you're 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 personal use, students and charitable organizations) or the fully functional 90-day business trial from:
http://www.asap-utilities.com/download-asap-utilities.php

One Response to “Tip: Get rid of unwanted spaces in your data”

  1. This is one of the most used features for me. You can never truly sort your data until you remove the characters that are not needed.

    It's common for me to work with many data sets of 100k rows. It's impossible to do this manually. Leading can be done but not trailing.

    Great simple feature

Pinterest