In this post, you will learn how to remove spaces from a text string in Excel.
Removing spaces from a text string –
If you pull data in from external databases and legacy systems, you will no doubt encoun-
ter text that contains extra spaces. Sometimes these extra spaces are found at the begin-
ning of the text, at the end of the text, or even between text strings (as in cell B6 shown
in Figure below).
Extra spaces are generally evil because they can cause problems in lookup formulas, chart-
ing, column sizing, and printing.
Figure below illustrates how you can remove superfluous spaces by using the TRIM function.
The TRIM function is relatively straightforward. Simply give it some text and it will remove
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, this function trims the
text in cell A1 and converts it to uppercase all in one step:
=UPPER(TRIM(A1))It’s important to note that the TRIM function was designed to trim only the ASCII space
character from text. The ASCII space character has a code value of 32. In the Unicode char-
acter set, however, there is an additional space character called the nonbreaking space char-
acter. 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’ll need to uti-
lize 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 all at one time in the following formula:
=TRIM(SUBSTITUTE(A4,CHAR(160),CHAR(32)))