Microsoft Office Tutorials and References
In Depth Information
• LOWER: Converts the text to all lowercase.
• PROPER: Converts the text to Proper Case. (The First Letter In Each Word Is Capitalized.)
Transforming data with formulas
Many of the examples in this chapter describe how to use functions to transform data in some way. For example,
you can use the UPPER function to transform text into uppercase. Often, you'll want to replace the original data
with the transformed data. To do so, Paste Values over the original text. Here's how:
1. Create your formulas to transform the original data.
2. Select the formula cells.
3. Choose Home⇒Clipboard⇒Copy (or press Ctrl+C).
4. Select the original data cells.
5. Choose Home⇒Clipboard⇒Paste⇒Values (V).
After performing these steps, you can delete the formulas.
These functions are quite straightforward. The formula that follows, for example, converts the text in cell A1 to
proper case. If cell A1 contained the text MR. JOHN Q. PUBLIC, the formula would return Mr. John Q. Public.
These functions operate only on alphabetic characters; they ignore all other characters and return them un-
The PROPER function capitalizes the first letter of every word, which isn't always desir-
able. Applying the PROPER function to a tale of two cities results in A Tale Of Two Cit-
ies. Normally, the preposition of wouldn't be capitalized. In addition, applying the
PROPER function to a name such as ED MCMAHON results in Ed Mcmahon (not Ed
McMahon). And, apparently, the function is programmed to capitalize the letter follow-
ing an apostrophe. Using the function with an argument of don't results in Don'T. But if
the argument is o'reilly, it works perfectly.
Extracting characters from a string
Excel users often need to extract characters from a string. For example, you may have a list of employee names
(first and last names) and need to extract the last name from each cell. Excel provides several useful functions
for extracting characters:
• LEFT: Returns a specified number of characters from the beginning of a string
• RIGHT: Returns a specified number of characters from the end of a string
• MID: Returns a specified number of characters beginning at any specified position within a string