Get Rid of Those #$@& Spaces In Imported Data - FREE Microsoft Excel Spreadsheet Tips and Tutorials

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!