Microsoft Office Tutorials and References
In Depth Information
This is the replacement text. If you want to specify which occurrence of
text to change, continue with Steps 8 and 9; otherwise, go to Step 10.
8. Enter a comma ( ,).
9. Enter a number that tells the function which occurrence to apply the
10. Type a ), and press Enter.
You can use SUBSTITUTE to remove spaces from text. In the second argument
(what to replace), enter a space enclosed in double-quote marks. In the third
argument, enter two double-quote marks with nothing in between them. This is
known as an empty string .
Giving text a trim
Spaces have a way of sneaking in and ruining your work. The worst thing is
that you often can’t even see them! When the space you need to remove is
at the beginning or end of a string, use the TRIM function to remove them.
The function simply clips any leading or trailing spaces from a string. It also
removes extra spaces from within a string — a sequence of two or more
spaces is replaced by a single space.
Figure 16-9 shows how this works. In Column A is a list of names. Looking
closely, you can see that some unwanted spaces precede the names in cells
A5 and A10. Column B shows the correction using TRIM. Here is the formula
in cell B5: =TRIM(A5).