Page 263 - Excel 2007 Bible

17_044039 ch12.qxp 11/21/06 11:05 AM Page 220
Part II
Working with Formulas and Functions
FIGURE 12.5
This worksheet uses formulas to extract the first name, last name, and middle name (or initial) from a list of names in
column A.
Removing titles from names
You can use the formula that follows to remove three common titles (Mr., Ms., and Mrs.) from a name. For
example, if cell A1 contains Mr. Fred Munster, the formula would return Fred Munster.
=IF(OR(LEFT(A1,2)=”Mr”,LEFT(A1,3)=”Mrs”,LEFT(A1,2)=”Ms”),
RIGHT(A1,LEN(A1) -FIND(“ “,A1)),A1)

Creating an ordinal number
An ordinal number is an adjective form of a number. Examples include 1st, 2nd, 5th, 23rd, and so on.
The formula that follows displays the value in cell A1 as an ordinal number:

=A13&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),”th”,
IF(OR(VALUE(RIGHT(A1))={1,2,3}),CHOOSE(RIGHT(A1),
“st”,”nd”,”rd”),”th”))
The formula is rather complex because it must determine whether the number will end in th, st, nd, or
rd. This formula also uses literal arrays (enclosed in brackets), which are described in Chapter 17.

Counting the number of words in a cell
The following formula returns the number of words in cell A1:
=LEN(TRIM(A1))-LEN(SUBSTITUTE( (A1),” “,””))+1

220