Chapter 11: Creating Formulas That Manipulate Text
247
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”))In many cases, you can eliminate the use of formulas and use the Text to Columns command to parse
strings into their component parts. This command is found in the Data Tools group of the Data tab. Text
to Columns displays the Convert Text to Columns Wizard, which consists of a series of dialog boxes
that walk you through the steps to convert a single column of data into multiple columns. Generally,
you want to select the Delimited option (in Step 1) and use Space as the delimiter (in Step 2), as shown
in the following figure.Splitting Text Strings without Using Formulas