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. 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. Combined, non-breaking spaces can be removed by nesting the CHAR function inside a SUBSTITUTE function. For example: 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.