"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Getting Rid of Those #$@& Spaces In Imported Data

If you work with data that has been imported into Excel from web pages, you may find yourself frustrated with stubborn, unwanted spaces in your data that you can't get rid of. One of my readers had just such a problem recently.

He had brought data from a web page into Excel but found that he couldn't perform calculations on the numeric data. He then discovered that there were blank spaces preceding the numbers, causing Excel to treat them as text. He tried the TRIM function, which got rid of some of the spaces, but there were other spaces that just wouldn't go away. Another method I often use to remove unwanted spaces from text is Edit and Replace 'space' with 'blank', however this doesn't even work on these stubborn spaces.

The problem here is that these are not just your ordinary spaces. These are non-breaking spaces (nbsp) that are used in HTML code, the language used to create web pages. Excel normally does a pretty good job handling web pages but this is one case where it falls down.

The solution is to use Edit and Replace, but instead using a 'space' in 'Find what', you need to enter the ALT key equivalent of the nbsp character.

Here's a sample worksheet. Right-click to download it. Otherwise it may open up in your browser.

  1. Highlight the cells containing the unwanted spaces
  2. Select Edit, Replace, click in 'Find what', hold down the ALT key and type 0160.
  3. Leave the 'Replace with' field blank. Click Replace All
That should do the trick!

Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.