Learn Why The Spaces In This Web Data Won't Go Away With This Microsoft Excel Tutorial
You tried highlighting column A and using Edit – Replace to replace every occurrence of a space with nothing. The blanks remain.
You tried the SUBSTITUTE function, as shown in Fig. 1033. The blanks remain.
If you go to cell A1 and type AJ Space 335 Space 986, the formula in column B will work, as shown in Fig. 1034.
What is going on? Why won’t the formula work on numbers pasted from the Web page?
Strategy: You can do some investigative work. Every character in the alphabet is assigned a number. A capital letter “A” is really character code 65. Normally, a space is character code 32.
In order to find the character code for a character, you can use the CODE function. To isolate the third character in cell A1, you would use =MID(A2,3,1). To find the character code for that value, you would use =CODE(MID(A2,3,1)).
As shown in Fig. 1035, this formula confirms that the value typed into cell A1 contains a space (character code 32) in the third position.
Now, copy that formula down to the other cells. As shown in Fig. 1036, all of the other cells have a character code 160 in the third position!
This explains why your attempts to change a space to nothing wouldn’t work. The cells in A don’t contain spaces.
A little research shows that character 160 is a non-breaking space. This is a space where you do not want the browser to start a new line between those words. The Nbsp character is very common on Web pages.
So, how can you use Character 160 in Edit – Replace? Here is one method.
Method 1:
-
Go to a blank cell in the worksheet and use the =CHAR(160) formula, as shown in Fig. 1037. You won’t see anything in the cell, but the formula bar will show that you have a formula hidden there.
-
Copy this cell.
-
Select your range of Web data. From the menu, choose Edit – Replace, as shown in Fig. 1038.
-
In the Find what box, hit Ctrl+V to paste the non-breaking space. Again, you won’t see anything that is there. Leave the Replace with box blank. Choose Replace All.
Result: As shown in Fig. 1039, the unwanted spaces are removed.
Method 2:
The other solution is to use the SUBSTITUTE function, as shown in Fig. 1040.
Summary:
Although character 160 is the usual culprit for this problem, using the CODE function will allow you to find the character code for any such offending character.
Functions Discussed: =CODE(); =CHAR(); =SUBSTITUTE()
Images
