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

Excel trim function

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.

ASCII codeset table.
 Wikimedia Commons

Different typestyles support different ranges of ASCII codes. The standard table offers 127 possible values; typefaces must minimally support a 127-character ASCII map to be considered valid. But "extended" ASCII characters, called with additional codes, frequently add additional characters to your favorite fonts. In fact, the non-breaking space is, itself, an extended ASCII character, whereas a standard space is ... well, standard.

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 must be typed into the worksheet rather than using the functions' dialog boxes to enter the arguments.

The formula, assuming the data with the non-breaking spaces is in cell A1, is:

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

How the Formula Works

Each nested function performs a specific task:

  • The CHAR function stipulates the relevant 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

Given the order-of-operations logic of Excel formulas, the formula calls the SUBSTITUTE() function and tell it to replace every occurrence of CHAR(160) — the non-breaking space — with a standard space, CHAR(32), contained within cell A1. Then, the TRIM() function removes the standard spaces from the substituted string.

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.