Microsoft Office Tutorials and References
In Depth Information
Changing Text
TRIM takes just one argument — the text to be cleaned of leading and trailing
spaces. Here’s how it works:
1. Position the cursor in the cell where you want the result to appear.
2. Enter =TRIM( to begin the function entry.
3.Clickacellthatcontainsthetextthathasleadingortrailingspaces,or
enter the cell address.
4. Type a ), and press Enter.
Be on the lookout: Although you generally use it to remove leading and trailing
spaces, TRIM removes extra spaces in the middle of a string. If there are two
or more spaces next to each other, TRIM removes the extra spaces and leaves
one space in place.
This is usually a good thing. Most times you don’t want extra spaces in the
middle of your text, but what if you do? Here are a couple of alternatives to
remove a leading space, if it is there, without affecting the middle of
the string:
Formula to Remove
Leading Space
Comment
=IF(LEFT(E10,1)=“
“,SUBSTITUTE(E10,”
“,”“,1), E10)
If a space is found in the first position,
substitute it with an empty string;
otherwise, just return the original string.
If a space is found in the first position,
return the right side of the string, less
the first position. (See the section on LEN,
earlier in this chapter.)
=IF(LEFT(E10,1)=“ “,
RIGHT(E10,LEN(E10)
-1), E10)
Making a case
In school you were taught to use an uppercase letter at the start of a
sentence as well as for proper nouns. But that was a while ago, and now the
brain cells are a bit fuzzy. Lucky thing Excel has a way to help fix case, er
Case, um CASE — well, you know what I mean.
Three functions alter the case of text: UPPER, LOWER, and PROPER. All three
functions take a single argument — the text that will have its case altered.
Here are a few examples:
Search JabSto ::




Custom Search