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

Remove 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 you copy or import text data into an Excel worksheet, the spreadsheet occasionally retains extra spaces in addition to the content you've inserted. Normally, the TRIM function on its own can remove these unwanted spaces whether they occur between words or at the beginning or end of a text string. In certain situations, however, TRIM can't do the job.

On a computer, a space between words is not a blank area but a character—and there is more than one type of space character. One space character commonly used in Web pages that 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

Spaces are characters and each character is referenced by its ASCII code value.

ASCII stands for the American Standard Code for Information Interchange—an international standard for text characters in computer operating environments that creates one set of codes for 255 different characters and symbols used 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.

Removing Non-breaking Spaces

Remove non-breaking spaces from a line of text using the TRIM, SUBSTITUTE, and CHAR functions.

Because 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
  2. Click cell D3—this cell 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. The line of text Removing non-breaking spaces in Excel should appear in cell D3 without the extra spaces between the words.
  4. Click cell D3 to display the complete formula, which appears in the formula bar above the worksheet.

How the Formula Works

Each nested function performs a specific task:

  • 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

Considerations

If TRIM cannot get the job done, you may have problems other than non-breaking spaces, particularly if you're working with original source material rendered in HTML. When you paste the material into Excel, paste it as plain text to strip background formatting from the string and remove special formatting like characters that are rendered as white-on-white—which looks like a space, but isn't. Check, too, for embedded tabs, which may be substituted using the same formula as above, but replacing ASCII code 160 with 9.

SUBSTITUTE is useful for replacing any ASCII code with any other.