Separating First and Last Names

One of the more common tasks you may come across is breaking out data from inside a cell. I am often asked to separate first and last names that are joined together in one cell. In this example I show a simple way to separate first and last names. I use a series of formulas to accomplish this task, FIND, MID, LEFT, LEN, TRIM and ISERROR.

Last name formula used in Column B

First name formula used in Column C

=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))))

How do you accomplish this task?

No comments:

Post a Comment