How to split full name to first and last name in Excel?

Supposing 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.

shot-split-names-1arrow-bigshot-split-names-2

Here are two tricky methods to help you solve this problem:

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

arrow blue right bubbleSplit full name to first and last name with functions

You can use the Left function, Right function and Find function to spit a full name to the first name and last name with following steps:
Step 1: Enter the formula of =LEFT(A1,FIND(" ",A1,1)-1) in a blank cell, says Cell C1 in this case.

shot-split-names-3

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

shot-split-names-4

Step 3: Select the range of C1:D13, and click the Home >> Fill >> Down.

Now you will see the full name column is split to the first name column and last name column at once.

Click to know how to merge the first name column and last name column into one column.


arrow blue right bubbleSplit 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 guild you to split the full name column easily.
Step 1: Select the column that you will split, in this case it is A1:A13.

Step 2: Click the 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.

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

shot-split-names-8

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

shot-split-names-9

Step 6: Click the Finish button.

Then you will see the full name column in separated into first name column and last name column at once.

Click to know how to merge the first name column and last name column into one column.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment


Security code
Refresh