1. Computing

When the TRIM Function Doesn't Work

Removing Non-breaking Spaces with the TRIM, SUBSTITUTE, and CHAR Functions

By

TRIM Function Alternative

TRIM Function Alternative

© Ted French

When the TRIM Function Doesn't Work

Related Tutorial: Remove Extra Spaces Between Data in Excel with the TRIM Function

When text data is imported or copied into an Excel worksheet extra spaces can sometimes be included along with the words.

Normally the TRIM function can be used to remove these unwanted spaces whether they occur between words or at the beginning or end of a text string.

In certain instances, however, TRIM can't do the job.

On a computer, a space between words is not a blank area but a character, and, believe it or not, there is more than one type of space character.

One space character, commonly used in web pages, which TRIM will not remove is the non-breaking space (&nbsp).

If you have imported or copied data from web pages you may not be able to remove the extra spaces with the TRIM function if they are created by non-breaking spaces.

Non-breaking vs. Regular Spaces

As mentioned, spaces are characters and each character has a number known as its ASCII code or value.

ASCII stands for the American Standard Code for Information Interchange and it creates one set of codes for 255 characters and symbols for use in computer programs.

The ASCII code for a non-breaking space is 160. The ASCII code for a regular space is 32.

The TRIM function can only remove spaces that have an ASCII code of 32.

Example: Removing Non-breaking Spaces

This example will remove non-breaking spaces from a line of text using the TRIM, SUBSTITUTE, and CHAR functions.

Since the SUBSTITUTE, and CHAR functions are nested inside the TRIM function, the formula will be typed into the worksheet rather than using the functions' dialog boxes to enter the arguments.

  1. Copy the line of text below, which contains several non-breaking spaces between the words non-breaking and spaces, into cell D1:
    Removing non-breaking       spaces in Excel 

  2. Click on cell D3 - this is where the formula to remove those spaces will be located

  3. Type the following formula into cell D3:

    =TRIM(SUBSTITUTE(D1,CHAR(160),CHAR(32)))

    and press the Enter key on the keyboard

  4. The line of text Removing non-breaking spaces in Excel should appear in cell D3 without the extra spaces between the words

  5. When you click on cell D3 the complete function appears in the formula bar above the worksheet

How the Formula Works

The job of each function is:

  • the CHAR function is used to enter the ASCII codes for the two different spaces into the formula - 160 and 32

  • the SUBSTITUTE function replaces or substitutes all of the non-breaking spaces between the words with regular spaces

  • the TRIM function removes the extra regular spaces between words so that the statement appears normally in the worksheet

©2014 About.com. All rights reserved.