The data you use in Excel occasionally comes from an external data source, such as a mainframe database, a Web page, or a text file.

Unfortunately, the data from external sources such as these often comes with unwanted characters, the most common of which are extra spaces. Fortunately, Excel comes with a function called TRIM() that deletes extra spaces in a cell. Here’s the simple syntax:

TRIM(text)
text              The string from which you want the excess spaces removed.

So what exactly does the TRIM() function trim from the text string? Three things:
? All spaces before text.
? All spaces after text.
? Any two or more consecutive spaces within text, which are reduced in each case to a single space.

For example, if cell A1 contains a name or other text with excess spaces, the following formula returns the string with those spaces removed:

=TRIM(A1)

A slightly different scenario is when you want to remove all spaces from a cell. For example, you might want to use the text in another context where the spaces would be illegal (such as a Web page file name; some Web servers don’t allow spaces in file names). In that case, you can use the SUBSTITUTE() function:

SUBSTITUTE(text, old_text, new_text [,instance_num])

text
The original string that contains the substring you want to replace.

old_text
The substring you want to replace.

new_text
The substring you want to use as the replacement.

instance_num
The number of replacements to make within the string (the default is all instances).
If you want to use SUBSTITUTE() to remove spaces from a cell, use a space as the old_text argument and the null string as the new_text argument. Here’s an example formula that removes all the spaces from the string in cell A1:
=SUBSTITUTE(A1, “ “, “”)

When you apply this formula to data imported from a Web page, you may find that it doesn’t remove some spaces. That’s because many Web pages use the nonbreaking space character to ensure that the Web browser renders the space.

To get rid of nonbreaking space characters, take advantage of the fact that the character’s ANSI code is 160. This means you can remove it with the following formula:

=SUBSTITUTE(A1, CHAR(160), “”)

Finally, here’s a formula that removes both regular spaces and nonbreaking spaces from a cell:

=SUBSTITUTE(SUBSTITUTE(A1, CHAR(160), “”), “ “, “”)

Here, the nested SUBSTITUTE() function serves as the text argument for the main SUBSTITUTE() function.

• Dressing for Spring Skiing When it comes to dressing for going out and skiing during the spring, the key is going to be layering...
• Waxing a Snowboard pt 3 What happens next in the waxing process? Now what you are going to want to do is to spread the...
• 6 Tennis Tips and Tricks Tennis tips and tricks are like small tweaks that you can make to your game in order to improve your...
• Buy Gold: Investment Tips When it comes to finding the right investment opportunity for you and your family, you should look no farther than...
• 50 really useful Android tips and tricks [/caption] Android is a great little mobile operating system for the modern smartphone, but it can feel a little bewildering...