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
=LEFT(A2,IF(ISERROR(FIND(",",A2,1)),LEN(A2),FIND(",",A2,1)-1))
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?
