How to Split Names in Excel into Two Columns (4 Quick Ways)

If your Excel worksheet contains full names in a column and you want to split the names into two columns, then you are in a right place. In this article, you will learn 5 quick ways to split names in Excel into two columns.

Download Practice Workbook

Download the following Excel file for your practice.

4 Quick Ways to Split Names in Excel into Two Columns

Let’s introduce our sample dataset first. In the column (B5:B10), we have our full names. Our goal is to split these names into the First Names and Last Names columns.

1. Use Text to Columns Wizard to Split Names into Two Columns

The most common way to split the text into several columns is to use Convert Text to Columns Wizard. Just follow the steps below to apply this amazing trick.

Steps:

• Select the cells (B5:B10) that include the texts you need to split.

• Select Data > Data Tools > Text to Columns. A Convert Text to Columns Wizard window will appear.

• Choose Delimited > click on Next.

• Choose the Delimiters for your texts. In this example, the delimiter is space. Then, click on Next.

• Choose the Destination (C5) in the current worksheet where you want to split texts to display. Finally, click on Finish.

Here is the split data–

Read More: Separate First and Last Name with Space Using Excel Formula (3 Ways)

2. Split Names Using Flash Fill

The Flash Fill can split your texts by identifying the pattern. Just follow the steps below to learn this magic trick.

Steps:

• In the neighboring cell C5, type the 1st name of the 1st full name. In the next-down cell C6, type the 1st name of the 2nd full name. Continue this activity until you see the Flash Fill show you a suggestion list of the 1st names in grey color.

• Press ENTER. You will see the rest of the cells with respective 1st names.

Repeat the steps for the last names of the full names.

Finally, here is the result,

Read More: How to Split Names Using Formula in Excel (4 Easy Methods)

3. Excel Formulas to Split Names into Two Columns

We can split a full name into first and last names by applying some built-in Excel formulas.

3.1 Get the First Name

Combining LEFT and FIND functions together helps us to split a full name separated by space into two columns. Just follow the steps below to do this.

Steps:

• First, write down the following formula in an empty cell C5.

Here, the FIND function gives the location of the first space from the string B5 and The LEFT function returns the characters from the string which is before the first space. You need to minus 1 to get the data excluding space.

• Press ENTER. You will see the 1st name at Cell C5. Now, drag the Fill Handle to get the 1st names from the rest of the full names.

Finally, here is the result,

3.2 Get the Last Name

Combining RIGHT and FIND functions together helps us to split a name separated by space into two columns. Just follow the steps below to do this.

Steps:

• First, write down the following formula in an empty cell D5.

Here, LEN(B5) determines the length of the string in cell B5.

The FIND(“ ”, B5) gives the location of the space from the full name and finally, the RIGHT function returns the characters from the full name which is after the space.

• Press ENTER. You will see the last name at Cell D5. Now, drag the Fill Handle to get the last names from the rest of the full names.

Finally, here is the result,

Read More: How to Separate First Name Middle Name and Last Name in Excel Using Formula

4. Split Names Using Find & Replace

If you love the flexibility that comes with Find and Replace in Excel, you can use this magical strategy.

4.1 Get the First Name

Just follow the steps below.

Steps:

• Copy all the full names, and paste them into the neighboring column (C5:C10)  titled First Names.

• Select C5:C10, go to the Home tab > Find & Select > Replace. A Find and Replace dialog box will pop up. Or just press the CTRL+H key.

• Enter “  *” ( 1 space before asterisk symbol) at Find what box and leave blank at Replace with box. Click on Replace All. Now, close the window.

Here is the result,

4.2 Get the Last Name

Just follow the steps below.

Steps:

• Copy all the full names, and paste them to the neighboring column (D5:D10)  titled Last Names.

• Select D5:D10, go to the Home tab > Find & Select > Replace. A Find and Replace dialog box will pop up. Or just press the CTRL+H key.

• Enter “* ” ( 1 space after asterisk symbol) at Find what box and leave blank at Replace with box. Click on Replace All. Now, close the window.

Here is the result,

Read More: Excel VBA: Split First Name and Last Name (3 Practical Examples)

Conclusion

In this tutorial, I have discussed 4 quick ways to split names in excel into two columns. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.