Bhubon Costa
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.
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.
Step 2: Select the Text to Columns Option
- Go to the Data.
- Click on the Text to Columns.
Step 3: Choose the Delimited Option
- From the box, choose the Delimited option and click on Next.
Step 4: Mark the Space Option
- From the list, click on the Space.
Step 5: Choose a Destination Cell
- In the Destination box, click on the cell you want to get a result.
- Then, click on Finish to see the first result.
Step 6: Final Result
- To get the final values in full, repeat the process described previously.
Read More: How to Separate First Middle and Last Name in Excel Using Formula
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.
Step 2: Select the First Names
- Select cell C5 for the First Name.
Step 3: Apply the Flash Fill Option
- From the Data tab, click on the Flash Fill.
- Therefore, all the First Names will appear in the first column.
Step 4: Select the Middle Name
- Firstly, select the Middle Name in cell D5.
Step 5: Use the Flash Fill Option
- Go to the Data tab, and click on the Flash Fill.
- As a result, all the Middle Names will appear as shown in the image below.
Step 6: Select the Last Name
- Click on cell E5 to select the Last Name.
Step 7: Apply the Flash Fill Option
- Finally, go to the Data tab and click on the Flash Fill.
- Therefore, all the Last Names will appear in the third column.
Read More: How to Split Names Using Formula in Excel (4 Easy Methods)
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)
- Press Enter to see the First Name.
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)
- Then, press Enter to get the Middle Name.
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))
- Finally, press Enter to see the Last Name in the third column.
- Use the AutoFill Tool to auto-fill all cells as shown in the image below.
Read More: How to Split Names with Comma in Excel (3 Suitable Ways)
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.
