Copy first and last names into separate cells without retyping (Excel 2016/2013/2010)
If you have a spreadsheet containing a list of people’s names, and each first and last name is in a single cell, you can break the first and last names into separate cells without having to retype each name yourself. If your list is long, you’ll not only save time but also reduce the very likely possibility of introducing errors.
To start, open a worksheet that contains a list of first and last names in Column A. Ensure that Column B and Column C are empty. Make Row 1 a header row labeled Name. Label cell B1 First Name and cell C1 Last Name.
Let’s say A2 contains the name Janet O’Neal. Click in B2, then type =LEFT(A2,FIND(" ",A2)-1) in the Formula Bar and press [Enter]. To place the last name in C2, click in that cell and then type =RIGHT(A2,LEN(A2)-FIND(" ",A2)). To copy the formulas for every name on the list, select cells B2:C2 and drag the selection box in the lower-right corner of the cells to the end of the list of names.
If your list of names follows a Lastname, Firstname format (such as O’Neal, Janet), you’ll need to reformat the names to Firstname Lastname format (Janet O’Neal) before you follow the instruction above. To do this, insert a column to the right of your names. Assuming the name you wish to transpose is in A3, click in B3 and then type =MID(A3,FIND(",",A3)+2,LEN(A3)) & " " & LEFT(A3,FIND(",",A3)-1).
Click here to sign-up for more Etips: http://bit.ly/newhorizonsetips