
However, there are times when you want to keep spaces between words. Hitting Replace All (keyboard shortcut: Alt+ A) will remove any instances of a space in the data set that you selected.Īlthough this method is really quick and easy, it's only useful for data where you want ALL spaces removed. Make sure there is nothing in the Replace with field. On the Replace tab, place one blank space in the Find what field.

That will bring up the Find and Replace window. The keyboard shortcut for this is Ctrl + H. Then open the Find & Select dropdown menu. The first method for space hunting is to use the Find & Replace feature.Īfter highlighting the cells that you want to search through, go to the Home tab. That sounds like a sci-fi movie title, but it's just someone who can quickly locate and delete superfluous spaces in cell values.īased on my space hunting experience, here are five techniques I use frequently to clean up data. That's because they are not easy to spot, yet can cause frustrating calculation errors.Īfter many years of working with Excel, I've become a Space Hunter. You have to use a program which doesn't try to do that for you.Remove Blank Spaces AFTER.xlsm Space Huntingīlank spaces can be a headache for data analysts and formula writers.

(Note: Alt codes with a leading zero indicate a Unicode alt code.) Windows is translating the glyph from the alt code for you in the background. That's also why you can fire up PowerShell and type this:Īnd the result is true. Now try to run "powershell.exe -Command "''"". Even if you try "Get-Content text.txt -Encoding ASCII" you won't get the same output as you do from cmd.exe because PowerShell's ASCII encoding is actually code page 20127 (7 bit ASCII), not code page 437. Your output will be "ÿ" which is Windows-1252 or Unicode character 255 or 0xFF. Now run "powershell -Command "Get-Content text.txt"". That's code page 437's extended ASCII non-breaking space followed by carriage return and line feed. Now type "powershell.exe -Command "::ReadAllBytes('C:\text.txt')"". Hit F6 or Ctrl+Z and hit enter to finish the file. Run "copy con C:\text.txt" to create a new file from the console input. Run "chcp" and it should tell you that the active code page is 437. However, even on Windows 10 you can still get to a place where you're using the original code page of 437.įire up cmd.exe. Windows is converting extended ASCII 255 from code page 437 to either Windows-1252's non-breaking space, which is 0xA0 or 160, or UTF-16-LE's code page where non-breaking space is 0x00A0.

On those code page, non-breaking space is 0xA0 (160). The problem you're having is that you're using Windows, which uses either Windows-1252 or UTF-16-LE (Windows Unicode). As you say, DOS's code code page is 437, and character 255 is a non-breaking space on code page 437. Everything above that is extended ASCII specific to a code page.
