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

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


Get my FREE
Excel Tips

NEWSLETTER
Subscribe now to 'Spreadsheet Tips From An Excel Addict' and start receiving these time-saving Excel tips

Check your email in a few minutes. You will need to click on the confirmation link before you begin receiving the tips

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 run-of-the-mill blank 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 of using a 'space' in 'Find what', you need to enter the ALT key equivalent of the nbsp character (0160).

1. Highlight the cells containing the unwanted spaces;

2. Select Edit, Replace, click in 'Find what', hold down the ALT key and type 0160 (use the number keypad not the numbers on the top row of your keyboard);

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 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

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