Remove excess spaces from data in Microsoft Excel

 Jul 25, 2014

When data is imported into Excel, excess characters sometimes come with it. Non-printing characters are the hardest to detect as they are, of course, invisible. Spaces are the most common of the non-printing characters. Excel has a worksheet function called TRIM that can remove these unwanted spaces. It will remove spaces from before and after values, and also excess spaces (i.e. more than one) between words. spaces-excel-1 The above TRIM function is designed to remove the 7-bit ASCII space character (value 32). In basic English, it removes the character made by using the space bar on your keyboard. There is another character called a non-breaking space (value 160, which is often found in web pages) that TRIM doesn’t remove. To get rid non-breaking spaces, the SUBSTITUTE function in conjunction with the CHAR function can be used.
  • The CHAR function refers to a character by its value. For example '=CHAR(32)' is a space and '=CHAR(160)' is a non-breaking space.
  • The SUBSTITUTE function replaces one string of text with another.
Let's take a look at the SUBSTITUTE function by referring to the image below. If the values are to be replaced with nothing, then double quotation marks (“”) can be used. spaces-excel-2 Combined, non-breaking spaces can be removed by nesting the CHAR function inside a SUBSTITUTE function. For example: spaces-excel-3 If the aim was to replace non-breaking spaces with normal spaces the formula would read:
=SUBSTITUTE(CELL REF,CHAR(160),CHAR(32))
If the aim was to replace non-breaking spaces with normal spaces, but ensure that there were only one normal space between words, the formula would read:
=TRIM(SUBSTITUTE(CELL REF,CHAR(160),CHAR(32)))
Space may be the final frontier, but that doesn't mean it has to clutter up our spreadsheets! For more on functions that can manipulate text, see the New Horizons' Excel 2010 Level 4 training course.

How do your Excel skills stack up?   

Test Now  

About the Author:

Ben Kirk  

With over 16 years of experience working as a Desktop Applications specialist for a number of large education services providers, Ben is one of New Horizons most skilled and dynamic instructors. With his Advanced Diploma of Business Skills alongside his practical experience and expertise, Ben is able to provide insight and guidance to students at all skill levels across the entire Microsoft Office suite.

Read full bio
top
Back to top