Look Who it Was Random video

Excel - How Separate First And Last Name In Excel - Episode 1145

700 views · Published 16 November 2009 · 3:58 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: how to split surname and names in Excel column.

Welcome back to the MrExcel netcast, where we tackle your Excel questions and provide helpful solutions. In this episode, we're diving into the world of data analysis and how to handle large amounts of data. Our goal is to find a way to split names into first and last names using Excel's powerful tools.

Today's question comes from Diane in Michigan, who has a specific request for splitting names in her data set. She wants the first name in column G and the last name in column H. Sounds simple enough, right? Well, there's a catch. Some names have multiple parts, such as middle names or suffixes, which can complicate the process.

To start, we'll insert a couple of columns and use the Text to Columns feature to split the names. However, this method can be tricky as it often results in extra columns and requires manual sorting to fix any errors. Another approach is to use formulas, such as the LEFT and MID functions, to extract the first and last names. While this method may seem more straightforward, it can also have its challenges when dealing with names of varying lengths.

No matter which method you choose, it's important to be aware of potential errors and manually check for any mistakes. In Diane's case, we encountered issues with names that only had one part, causing problems with our formula. So, it's crucial to double-check and make any necessary adjustments.

In the end, we were able to successfully split the names into first and last names, with the first name in column G and the last name in column H. However, as with any data analysis, it's essential to be cautious and thorough to ensure accurate results. Thank you, Diane, for sending in your question, and thank you for tuning in to another netcast from MrExcel. We'll see you next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Break Name into First Name and Last Name
(00:35) Using Text to Columns to separate data
(01:01) Sorting data to fix errors
(01:36) Alternative method using formulas
(02:38) Converting formulas to values
(03:07) Manual checks and adjustments
(03:21) Clicking Like really helps the algorithm

This video answers these common search terms:
how to split columns by first name last name excel
how to split surname and names on excel column
how to divide surname and name in excel
how to separate surname and first name on excel
how to split name and surname in excel
how to divide name and surname in excel
how to split a name and surname in excel
how to break first and last name in excel
how to break out first and last name in excel
how to break up first and last name excel
how to split names in an excel sheet
how to split a name in two in excel
how to split a name into two columns in excel
how to split names to two columns in excel
how to seperate a last name and first name in excel
how separate first and last name in excel


Diane from Michigan wants to split a column of names into two columns. Episode 1145 shows two approaches and the problems with each.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152751/

More from this channel