Excel 2010 Bible

Part II: Working with Formulas and Functions

246

The formulas that follow all assume that the name appears in cell A1.

You can easily construct a formula to return the first name:

=LEFT(A1,FIND(“ “,A1)-1)

This formula returns the last name:

=RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-

LEN(SUBSTITUTE(A1,” “,””)))))

The next formula extracts the middle name and requires that you use the other formulas to extract

the first name and the last name. It assumes that the first name is in B1 and the last name is in D1.

Here’s what it looks like:

=IF(LEN(B1&D1)+2>=LEN(A1),””,MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1&D1)-2))

As you can see in Figure 11.5, the formulas work fairly well. There are a few problems, however,

notably names that contain four “words.” But, as I mentioned earlier, you can clean up these cases

manually.

On the CD
This workbook, named extract names.xlsx, is available on the companion CD-ROM. n

FIGURE 11.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.