Suppose you have a name roster, as the first screen shot shows in single column below, and you need to split the full name to the first name column and last name column as the second screen shot shows.

List of Names

Here are two solutions to accomplish this task:

Method 1 - Split full name to first and last name with functions

Step 1: Enter the formula of =LEFT(A1,FIND(", ",A1,1)-1) in a blank cell, for example, Cell B1 in this case.

=LEFT(A1,FIND(

Step 2: Enter the formula of =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) in another blank cell, i.e. Cell C1.

 =RIGHT(A1,LEN(A1)-FIND(

Step 3: Select the range of B1:C13, and click the Home > Fill > Down.

Home - Fill - Down

Method 2 - Split full name to first and last name with Text to Column command

It is not easy to remember the function. Please do not worry about it. The second way will guide you to split the full name column easily.

Step 1: Select the column that you will split, in this case it is A1:A15.

Step 2: Click the Text to Column button under Data Tab.

Text to Column button under Data Tab

Step 3: In the Convert Text to Columns Wizard – Step 1 of 3 dialog box, check the Delimited option and click Next button.

Delimited option and click Next button

Step 4: In the Step 2 of 3, only check the Comma option in the Delimiters section, and click Next button.

 Comma option in the Delimiters section, and click Next button

Step 5: in the step 3 of 3, check the Text option in the Column data format section, and specify the Destination cell.

 Destination cell

Step 6: Click the Finish button.

You will now see the full name column is separated into first name and last name columns at once.