If you have ever had problems sorting, filtering, or manipulating data in a Microsoft® Excel® workbook and realized that it was caused by stubborn leading spaces in the cells, the following steps will show you how to get rid of the spaces quickly next time.

If you’re wondering what causes the spaces, here are two possibilities:

• You may have mistakenly inserted extra spaces in your data by pressing the space bar more than once; or
• You may have imported data from external sources which has embedded characters in the text that may not be easily noticeable making the results difficult to understand.

The next time you have this issue you can quickly remove unwanted spaces and characters using a combination of the TRIM and SUBSTITUTE functions. The TRIM function removes leading and trailing spaces in data but not characters. The SUBSTITUTE function replaces non-breaking characters with characters that the TRIM function can remove.

1. To remove leading and trailing spaces from the example workbook; the TRIM function will be used.

a. Select cell C2.
b. Type the formula =TRIM(B2).
c. Press Enter and copy the formula down.

2. As you can see the spaces have not yet been removed. This is because the TRIM and SUBSTITUTE functions must be nested.The stubborn characters set number 160 will be replaced with 32 then can be removed by the TRIM function.

Note:

• In order to transmit information between computer systems a standard system that assigns numbers to letters of the alphabet, digits and characters was developed.
• Therefore the character set number 32 represents a space.
• The character set 160 represents a non-breaking space character that can’t be removed by the TRIM function. It is commonly used in web pages.

a. Select cell C2 and delete the formula.
b. Enter the following formula and then copy it down:

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

The stubborn leading spaces and characters will be removed so you can easily manipulate your data. Essentially, the SUBSTITUTE function in our example replaced the non-breaking character with a space which the TRIM function can remove.

(Appies to Microsoft® Excel® 2007, 2010 and 2013)

11 tips on Excel PivotTables and PivotCharts!

• Slicers
• Trendlines
• Timelines
• Rank Smallest to Largest
• PivotTable Groups
• PowerPivot add-in
  
  DOWNLOAD NOW!