Separating names into last and first names

I had encountered this problem while carrying out mail merge. Usually names are stored in a single field. In order to carry out mail merge for a letter/mailer, last names needs to be stored in a separate field to faciliate merge as Dear Mr/Ms last_name

The original list is stored in column A whereas the desired lists in columns B and C.
Formula used in cell B2 is =RIGHT(A2,LEN(A2)-FIND(” “,A2))
Formula used in cell C2 is =LEFT(A2,FIND(” “,A2)-1)
Dragging these formulas give us the desired results in columns B and C

Alternative solution

1. Select A2:A8

2. From toolbar –>Data –> Text to Columns… –>Delimited–>Space–> Finish

Make sure that column B has no data to begin with. If there is some data already, insert a blank column B.

Appears as First Name, Last Name in columns A and B

Read Full Post »