What to Do When Excel's TRIM Function Doesn't Work

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

Excel trim function
TRIM-SUBSTITUTE-CHAR Formula to Remove Non-breaking Spaces in Excel. © Ted French

When the TRIM Function Doesn't Work

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 on its own 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  ( ;).

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

As shown in the image above, 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
  1. Click on cell D3 - this is where the formula to remove those spaces will be located
  2. Type the following formula into cell D3:

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

    and press the Enter key on the keyboard
  3. The line of text Removing non-breaking spaces in Excel should appear in cell D3 without the extra spaces between the words
  4. When you click on cell D3 the complete formula 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
Format
mla apa chicago
Your Citation
French, Ted. "What to Do When Excel's TRIM Function Doesn't Work." ThoughtCo, Jun. 27, 2017, thoughtco.com/when-excels-trim-function-doesnt-work-3123658. French, Ted. (2017, June 27). What to Do When Excel's TRIM Function Doesn't Work. Retrieved from https://www.thoughtco.com/when-excels-trim-function-doesnt-work-3123658 French, Ted. "What to Do When Excel's TRIM Function Doesn't Work." ThoughtCo. https://www.thoughtco.com/when-excels-trim-function-doesnt-work-3123658 (accessed November 17, 2017).