Page 258 - Excel 2007 Bible

17_044039 ch12.qxp 11/21/06 11:05 AM Page 215
Creating Formulas That Manipulate Text
Transforming Data with Formulas
any of the examples in this chapter describe how to use functions to transform data in some way. For
Mexample, 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, use the Paste Special dialog box. Specifically,
follow these steps:
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.
This procedure replaces the original data with the transformed data; then you can delete the formulas.
These functions operate only on alphabetic characters; they simply ignore all other characters and return 12
them unchanged.
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:
n LEFT returns a specified number of characters from the beginning of a string.
n RIGHT returns a specified number of characters from the end of a string.
n MID returns a specified number of characters beginning at any position within a string.
The formula that follows returns the last 10 characters from cell A1 (if A1 contains fewer than 10 charac-
ters, the formula returns all text in the cell):
=RIGHT(A1,10)
This next formula uses the MID function to return five characters from cell A1, beginning at character posi-
tion 2. In other words, it returns characters 2–6.
=MID(A1,2,5)
The following example returns the text in cell A1 with only the first letter in uppercase. It uses the LEFT
function to extract the first character and convert it to uppercase. This then concatenates to another string
that uses the RIGHT function to extract all but the first character (converted to lowercase). Here’s what it
looks like:
=UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1)
If cell A1 contained the text FIRST QUARTER, the formula would return First quarter.

NOTE This is different than the result obtained using the PROPER function. The PROPER function
NOTE
makes the first character in each word uppercase.

215