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, this tip will show you how to remove spaces quickly the next time. If you're wondering what causes the spaces, there 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 have embedded characters in the text that may not be easily noticeable making the results difficult to understand.

The next time you're faced with this dilemma, 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. You are welcome to download the sample workbook to practice this exercise. Applies To: Microsoft  Excel  2007, 2010 and 2013. 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 space—which the TRIM function can remove. Are you a fan of Sage Intelligence, or maybe you just want to discover a few more Excel tips? Well then here's a webcast you don't want to miss: In this session*, Dave and Darlene Smith from Phase One Computing will take you through the steps of creating a commissions report from scratch—while sharing some Sage Intelligence and Microsoft Excel tips along the way. JOIN THE LIST! *This is an open platform session which would be relevant for all Sage customers and business partners using Sage Intelligence.