Microsoft Office Tutorials and References

In Depth Information

**Parsing Names**

Parsing Names

Suppose that you have a list of people’s names in a single column. You have to separate these

names into three columns: one for the first name, one for the middle name or initial, and one for

the last name. This task is more complicated than you might initially think, because not every

name in the column has a middle name or middle initial. However, you can still do it.

This task becomes a lot more complicated if the list contains names with titles (such

as Mrs. or Dr.) or names followed by additional details (such as Jr. or III). In fact, the

following formulas don’t handle these complex cases. However, they still give you a

significant head start if you’re willing to do a bit of manual editing to handle the

special cases.

The following formulas 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 following formula extracts the middle name (if any). It assumes that the first name is in B1

and the last name is in D1:

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

As you can see in Figure 106-1, the formulas work fairly well. A few problems occurred, however —

notably, names that contain either one word or more than three words. But, as I mention earlier in

this tip, you can clean these cases up manually.