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.