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.
 
Search JabSto ::




Custom Search