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 following screen shot shows.

doc split names 1

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

Split full name to first and last name with functions

Split full name to first and last name with Text to Column command

Split full name to first and last name with Kutools for Excel quickly and easily


Hint


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:

1. Enter the formula of =LEFT(A2,FIND(" ",A2,1)-1) in a blank cell, says Cell C2 in this case.

doc split names 2

2. Enter the formula of =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)) in another blank cell, Cell D2.

doc split names 3

3. Select the range of C2:D15, 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.

doc split names 4

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.

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

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

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

doc split names 5

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

doc split names 6

5. In the step 3 of 3, check the Text option in the Column data format section, and specify the Destination cell where you want to put the results. See screenshot:

doc split names 7

6. Click the Finish button. Then you will see the full name column has been 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.


arrow blue right bubbleSplit full name to first and last name with Kutools for Excel quickly and easily

If you are tired with the above two methods, here, I can recommend you a useful tool – Kutools for Excel, with its Split Names feature, you can quickly split the full names to first and last names, or split full names to first, middle and last names as you need.

Kutools for Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days. Get it Now .

After installing Kutools for Excel, please do as follows:

1. Select the name range that you want to split.

2. Click Kutools > Text > Split Names, see screenshot:

doc split names 8

3. In the Split Names dialog box, specify the Split types you want, if you want to split the full name to first and last name, please check First name and Last name, see screenshot:

doc split names 9

4. Then click OK button, and another dialog will appear, please select a cell where you want to put the result in the dialog, see screenshot:

doc split names 10

5. And click OK button, the full names have been split into first name column and last name column, see screenshot:

doc split names 11

Note: If your full name contains first, middle and last names needed to be split into three columns separately, you just need to check First name, Middle name and Last name in the Split Names dialog box and you will get the following result:

doc split names 12
 1
doc split names 13

Click to know more about this Split Names feature.

Free Download Kutools for Excel Now

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


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase

Add comment


Security code
Refresh