When the TRIM / CLEAN Function Doesn't Work

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-breakingand spaces, into cell D1:

   Removing non-breaking&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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