Split Cells with Text to Columns in Excel

Bottom Line: Learn how to split a column of names into first and last names with the Text to Columns feature of Excel.

Skill Level: Intermediate

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

If you'd like to practice using the same workbook I use in the video, you can download the file here:

Excel File Icon 2021 xlsx Split Names with Text to Columns.xlsx

Splitting Names into Separate Columns

It's really common to have a column of full names that you want to separate into a first name column and a last name column. Excel has an easy built-in feature to do this called Text to Columns. It allows you to split cells by character. It has some limitations compared to other options, which I'll talk about more at the end, but I'd like to walk you through how to use Text to Columns for this task.

As you can see, I'm starting with a simple table that has a Full Name column.

Table with Full Name Column

My first step is to duplicate the column. I'm doing this because I want to keep a column for the full name in addition to having columns for both first and last names. If you don't want to keep a Full Name column, you can skip the duplication step.

To duplicate the column, just copy the column and paste it into a blank column.

Duplicate the Full Name Column

With the new column selected, click the Text to Columns button on the Data tab.

Text to Columns

This will open up the three-part Text to Columns Wizard. For the first step, ensure that the Delimited button is selected and hit Next.

Select Delimited in the Text to Columns Wizard..

In the second step, select Space for the type of delimiter that we want to split by. This will break up the first names from the last names because they are already separated by a space. You'll notice that the wizard gives a little preview for how the split will look.

Convert Text to Columns Wizard step 2

Nothing is required on the third step of the wizard, so just hit Finish on the second step.

You now have two columns instead of one. Of course, you can rename the columns to First and Last and move them around in your table if you wish.

Split Column

Some Disadvantages of Text to Columns

As you can tell, this method for splitting columns is super easy and is great for quick, one-time uses. But if you expect your source data to constantly have edits or additions, it's not the best method.

In that case, I recommend using Power Query to split your columns. I've demonstrated how to do that in this post: How to Split Cells and Text in Excel with Power Query. Using Power Query, updating the data in your split columns is instantaneous whenever you refresh your query. Using Text to Columns, you would essentially have to delete your new columns and start again if you wanted to update.

Another instance of the Power Query method being superior to Text to Columns is when some of the Full Name entries include a middle name.

When we use Text to Columns and some of the entries have middle names, the names get split into three columns, which is good. But for the entries that don't have middle names, the last name gets put into the second column instead of the third, which is bad.

Text to columns for both two name and three names entries

Using Power Query, you can split off the the last names first, then the first names can be separated, leaving the middle column populated only when it's applicable.

Conclusion

Again, I would recommend Power Query over Text to Columns in most cases, but I want you to know Text to Columns because it's an easy and simple solution when you just need to split two names on a data set that won't require updates.

I hope this explanation for splitting cells by a delimiter in order to extract names has been helpful for you. I welcome your questions and feedback in the comments. Until next time!

  • These are few examples from Europe, BOLD are the Family Names

    Heinz LYNER
    Ludwig F. GROSS
    Hans-Peter KELLER
    J.F. MERCIER
    Ester LEUSCHNER-SCHAYAN
    Maria Theresa MÜLLER MATTER
    Herbert VON KARAJAN
    Dr. Jörg MITTELSTEN SCHEIDT

    even Power Query will have some problems (not with the first ones, but they have to work too..)

    • Hi Heinz,
      Great point! If the family (last) names are all capitalized words, then you do have a pattern here. There is probably a way for Power Query or an Excel formula to identify the capitalized words and then split the column based on that logic or character number where the capitalized word starts.

      It can definitely be done with VBA.

      I’ll have to give it some thought, but hopefully, that helps get you started.

      Thanks again and have a nice day! 🙂

  • Don’t forget Flash Fill – type in the first of the first names in the First Name column and then select the cell and CTRL+E or Flash Fill button on the Data tab (in the Data Tools group of buttons). Excel will attempt to enter the rest of the first names in the column.

    One of the features of Flash Fill I particularly like is that you can correct an erroneous entry and Excel will attempt to “learn” from your correction and correct similar errors.

    Testing Heinz’s example needed 4 corrections to the surnames Flash Fill suggested – not too impressive in a list of 7 but maybe 100 names wouldn’t have needed much more. Jon’s example needed no corrections.

    Flash Fill won’t always work, but it only takes a few seconds to try it and see what results you get.

    Like Text to Columns, Flash Fill inserts a text value for each name, so if the full name changes it won’t update the first name column.

  • What is the formula that you would use to fill in the first and last names in the correct column?

    • Hi! If I understand your Q, simply look at step 2 above. Selecting ‘Text to Columns’ from the ribbon acts as a formula.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >