How to Split Names into Three Columns in Excel (3 Methods)

When you have a list of complete names and wish to separate them into first, middle, and last names, you may use Excel features to divide the cells. In this tutorial, we will show you how to split names in Excel into three columns.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Suitable Ways to Split Names into Three Columns in Excel

A sample data collection containing a list of complete names is shown in the figure below. We must now split the names into First Name, Middle Name, and Last Name. To do this, we will use the Text to Columns and Flash Fill options, as well as combine many functions such as the LEFT, RIGHT, and MID functions.

Sample Data

1. Use the Text to Columns Option to Split Names into Three Columns in Excel

In the beginning section, we will use the Text to Columns option to split the Names. To do this, follow the outlined steps below.

Step 1: Select Cell

  • Firstly, click on a cell to select it.

Suitable Ways to Split Names in Excel into Three Columns

Step 2: Select the Text to Columns Option

  • Go to the Data.
  • Click on the Text to Columns.

Suitable Ways to Split Names in Excel into Three Columns

Step 3: Choose the Delimited Option

  • From the box, choose the Delimited option and click on Next.

Suitable Ways to Split Names in Excel into Three Columns

Step 4: Mark the Space Option

  • From the list, click on the Space.

Suitable Ways to Split Names in Excel into Three Columns

Step 5: Choose a Destination Cell

  • In the Destination box, click on the cell you want to get a result.

Suitable Ways to Split Names in Excel into Three Columns

  • Then, click on Finish to see the first result.

Suitable Ways to Split Names in Excel into Three Columns

Step 6: Final Result

  • To get the final values in full, repeat the process described previously.

Suitable Ways to Split Names in Excel into Three Columns


2. Apply Flash Fill Option to Split Names into Three Columns in Excel

Flash Fill is another option you can choose to split the Names. Simply follow the procedure below to achieve the thing.

Step 1: Write the Names into Different Columns

  • Firstly, write the First Name, Middle Name, and Last Name respectively in three different columns.

Suitable Ways to Split Names in Excel into Three Columns

Step 2: Select the First Names

  • Select cell C5 for the First Name.

Suitable Ways to Split Names in Excel into Three Columns

Step 3: Apply the Flash Fill Option

  • From the Data tab, click on the Flash Fill.

Suitable Ways to Split Names in Excel into Three Columns

  • Therefore, all the First Names will appear in the first column.

Suitable Ways to Split Names in Excel into Three Columns

Step 4: Select the Middle Name

  • Firstly, select the Middle Name in cell D5.

Suitable Ways to Split Names in Excel into Three Columns

Step 5: Use the Flash Fill Option

  • Go to the Data tab, and click on the Flash Fill.

Suitable Ways to Split Names in Excel into Three Columns

  • As a result, all the Middle Names will appear as shown in the image below.

Suitable Ways to Split Names in Excel into Three Columns

Step 6: Select the Last Name

  • Click on cell E5 to select the Last Name.

Suitable Ways to Split Names in Excel into Three Columns

Step 7: Apply the Flash Fill Option

  • Finally, go to the Data tab and click on the Flash Fill.

Suitable Ways to Split Names in Excel into Three Columns

  • Therefore, all the Last Names will appear in the third column.

Sample Data


3. Combine LEFT, RIGHT, and MID Functions to Split Names into Three Columns in Excel

In combination with the LEFT, the RIGHT, and the MID functions we can split names into three different columns. To do the task, simply follow the steps below.

Step 1: Apply LEFT Function

  • To extract the First Name from cell B5, type the following formula with the LEFT function.
=LEFT(B5,SEARCH(" ",B5,1)-1)

Sample Data

  • Press Enter to see the First Name.

Sample Data

Step 2: Use MID Function

  • For extracting the Middle Name from cell B5, type this following formula with the MID function.
=MID(B5,SEARCH(" ",B5) + 1, SEARCH(" ", B5, SEARCH(" ", B5) + 1) - SEARCH(" ", B5) -1)

Sample Data

  • Then, press Enter to get the Middle Name.

Sample Data

Step 3: Perform RIGHT Function

  • To separate the Last Name from cell B5, type the following formula with the RIGHT function.
=RIGHT(B5, LEN(B5) - SEARCH(" ", B5, SEARCH(" ", B5, 1)+1))

Sample Data

  • Finally, press Enter to see the Last Name in the third column.

Sample Data

  • Use the AutoFill Tool to auto-fill all cells as shown in the image below.

Sample Data


Conclusion

To conclude, I hope this article has given you some useful information about how to split names in Excel into three columns. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, The Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo