Remove Hidden Spaces from Excel - Including Char(160)

Add to Favorites
Author: | Edits: don

How to quickly and easily remove the hidden space character from Excel, CHAR(160) - this tutorial also includes how to use Find/Replace to remove the space quickly from many cells in a worksheet or the entire worksheet at once.

The hidden space character, CHAR(160), often appears in your spreadsheet when data is imported from another program or when you copy/paste data from the internet. This character is technically called the non-breaking space character, but, honestly, its an evil monster that is difficult to detect and remove.

Once you learn how to remove this character, particularly with the last method that I show in the video, Find/Replace, you should start applying it to all of your imported copy/pasted data automatically before you process it just to ensure that there are no sneaky spaces in there that will cause you trouble in your formulas and functions later.

The worst part about this space character is that it looks exactly the same as the regular space, but formulas and functions do not treat it the same as a regular space.

This is another one of those pains that we just have to learn to live with in Excel.

 

Question? Ask it in our Excel Forum


Excel Function: CHAR(), LEN, LENB(), SUBSTITUTE(), TRIM()
Downloadable Files: Excel File