Formula Excel : Removing Spaces from a Text String

If you pull data in from external databases and legacy systems, you will no doubt encounter text that contains extra spaces. Sometimes these extra spaces are found at the beginning of the text, whereas at other times, they show up at the end. Extra spaces are generally evil because they can cause problems in lookup formulas, charting, column sizing, and printing.

Image 3-3 illustrates how you can remove superfluous spaces by using the TRIM function.

Image 3-3: Removing excess spaces from text.

How it works

The TRIM function is relatively straightforward. Simply give it some text and it removes all spaces from the text except for single spaces between words. As with other functions, you can nest the TRIM function in other functions to clean up your text while applying some other manipulation. For instance, the following function trims the text in cell A1 and converts it to uppercase all in one step:

=UPPER(TRIM(A1))

Note : 
The TRIM function was designed to trim only the ASCII space character from text. The ASCII space character has a code value of 32. The Unicode character set, however, has an additional space character called the nonbreaking space character. This character is commonly used in web pages and has the Unicode value of 160.

The TRIM function is designed to handle only CHAR(32) space characters. It cannot, by itself, handle CHAR(160) space characters. To handle this kind of space, you need to use the SUBSTITUTE function to find CHAR(160) space characters and replace them with CHAR(32) space characters so that the TRIM function can fix them. You can accomplish this task all at one time with the following formula:

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

For a detailed look at the SUBSTITUTE function, see Formula 18: Substituting Text Strings.

Categories: Formula