Excel 2010 Bible

Part II: Working with Formulas and Functions

240

These functions are quite straightforward. The formula that follows, for example, converts the text

in cell A1 to proper case.

=PROPER(A1)

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 simply ignore all other characters and

return them unchanged.

These functions aren’t perfect, and they sometimes produce undesired results. For example, this

formula returns Don’T:

=PROPER(“don’t”)

Apparently, the PROPER function is programmed to always capitalize the letter following an apos-

trophe. If the argument is “o’reilly”, the function 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 pro-

vides several useful functions for extracting characters:

l LEFT returns a specified number of characters from the beginning of a string.

l (^) RIGHT returns a specified number of characters from the end of a string.
l MID returns a specified number of characters beginning at any position within a string.
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. Specifically, follow these steps:

1. Insert a new temporary column for formulas to transform the original data.


2. Create your formulas in the temporary column.


3. Select the formula cells.


4. Choose Home ➪ Clipboard ➪ Copy (or press Ctrl+C).


5. Select the original data cells.


6. Choose Home ➪ Clipboard ➪ Paste ➪ Values (V).

This procedure replaces the original data with the transformed data; then you can delete the temporary

column that holds the formulas.

Transforming Data with Formulas