A frequent task many people encounter when working with lists of data in Excel is splitting full names into the first-name and last name components. For example, a user may have full names like "Pearson, Charles H" in column A, and needs to put the last name in column B, the first name in column C, and the middle initial in column D. This page describes some worksheet formulas and VBA procedures you can use to accomplish this. For a related procedure for working with telephone numbers, see Parsing Telephone Numbers.
All of the functions and procedures on this page assume that your data is formatted as "LastName, FirstName MiddleName". For example, "Pearson, Charles H". Suffixes like "Jr" are permitted in the LastName, as are two word last names (e.g., St James) and hyphenated last names (e.g., Smith-Cross). For example, all the the following names are valid.
Pearson
Pearson, Charles
Pearson, Charles H
Pearson, Charles Henry
Pearson Jr, Charles
St James, Michael
St James Jr, Michael
Smith-Cross, Linda K
Worksheet Functions
This section describes several worksheet functions you can use to split full names into the first and last name components.
To return the last name of the full name in A2, use the following formula.
=LEFT(A2,IF(ISERROR(FIND(",",A2,1)),LEN(A2),FIND(",",A2,1)-1))
To return the first name of the full name in A2, use the following formula.
=TRIM(IF(ISERROR(FIND(",",A2,1)),A2,MID(A2,FIND(",",A2,1)+1,
IF(ISERROR(FIND(" ",A2,FIND(",",A2,1)+2)),LEN(A2),
FIND(" ",A2,FIND(",",A2,1)+2))-FIND(",",A2,1))))
To return the middle name of the full name in A2, use the following formula.
=TRIM(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,
FIND(" ",A2,FIND(",",A2,1)+2))),LEN(A2),
FIND(" ",A2,FIND(" ",A2,FIND(",",A2,1)+2))-1)))
More here:
http://www.cpearson.com/excel/FirstLast.htm
Please post back if you have any more problems or questions.
