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.

 


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

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.