Split first and last name in excel is common task while processing Full Names in the customer information. When working with data in Excel, you might often find yourself in a situation where you have a list of full names in a single column, but you need to separate them into first and last names for better data management. Whether you’re organizing a contact list, preparing a mailing list, or managing employee records, splitting full names into separate columns can be incredibly useful.

In this guide, we’ll walk you through the process of splitting first and last names in Excel using formulas, Functions, Commands and VBA complete with an example to help you understand the steps.

Excel Formula to Split First and Last Name: Step by Step Guide

Here is the Excel Formula to Split first and last name in Excel. Our Step by Step Explanation to Split Names in Excel provided with clear steps and explain each step using example data. You can copy the data into your worksheet and use our Excel Formula to Split Full Names into First Name and Last Name.

Excel Formula to Split First and Last Name

Example Data

Imagine you have the following data in your Excel worksheet. We have provided the Example file at the end of this article to download and explore the examples.

Full Name
John Doe
Jane Smith
Emily Johnson
Mark Zuckerberg

This data is located in column A, starting from cell A1, which contains the header, and from A2 to A5, you have the list of full names.

Step 1: Prepare Your Excel Sheet

Before splitting the names, make sure you have enough empty columns to the right of your full names column to accommodate the split data. For splitting into first and last names, you’ll need one empty column.

Step 2: Use the Formula to Split the First Name

  1. Click on the cell where you want the first name to appear. If your full names start in cell A2, then click on cell B2.
  2. Enter the following formula: =LEFT(A2, FIND(" ", A2) - 1)

This formula works by finding the space character in the full name and then extracting everything to the left of it, which is typically the first name.

Step 3: Use the Formula to Split the Last Name

  1. Click on the cell where you want the last name to appear. Following our example, this would be cell C2.
  2. Enter the formula: =RIGHT(A2, LEN(A2) - FIND(" ", A2))

This formula finds the space in the full name and then extracts everything to the right of it, which should be the last name.

Step 4: Copy the Formulas Down

Drag the fill handle (the small square at the bottom right corner of the cell) down to fill in the formulas for the rest of your data.

Output

After applying the formulas, your Excel sheet should now look like this:

Full Name First Name Last Name
John Doe John Doe
Jane Smith Jane Smith
Emily Johnson Emily Johnson
Mark Zuckerberg Mark Zuckerberg

 

Top 7 Easy Ways to Split First and Last Name:

Excel offers several more methods and tools to split Names and text data, catering to different user preferences and specific scenarios. Here are additional methods that complement the ones we’ve discussed, you can implement one of this based on your requirement and data.

Method 1: Shortcut to Split first and last name in Excel – Flash Fill

Flash Fill is a smart Excel feature that recognizes patterns in your data and automatically fills the rest of the column accordingly. It’s particularly useful for quickly splitting text without needing to write any formulas.

  1. Prepare Your Excel Sheet: Ensure you have your full names in one column and adjacent empty columns for the first name and last name.
  2. Manually Enter the First Name: In the column next to your full names, manually enter the first name of the first person in your list.
  3. Use Flash Fill:
    • Immediately after typing the first name, press Ctrl+E to activate Flash Fill. Excel will automatically fill the rest of the column with first names extracted from the full names.
    • Repeat the process for last names in the next column, manually typing the last name for the first entry, then pressing Ctrl+E.

Flash Fill is an excellent tool for quickly splitting names, especially when the data follows a consistent pattern.

Method 2: Separate First Name and Last Name Using the Text to Columns Command

Text to Columns is a feature in Excel that allows you to split the contents of a column into multiple columns, based on a specific delimiter, such as a space, comma, or other characters.
Split First and Last Name in Excel - Text to Columns Command

  1. Select Your Data: Highlight the column that contains the full names you want to split.
  2. Open Text to Columns:
    • Go to the Data tab on the Ribbon.
    • Click on Text to Columns. This will open the Text to Columns wizard.
  3. Choose Delimited: Select the ‘Delimited’ option, then click Next. This is because our data is separated by a delimiter (space).
  4. Set Delimiters: In the Delimiters section, check the ‘Space’ option, and uncheck any other selected delimiters. Then, click Next.
  5. Finish: Choose the destination cell where you want the split data to begin (make sure it’s in an empty column to avoid overwriting existing data). Click Finish.

Your data will be separated into multiple columns based on the space between the first and last names.

Method 3: Using Power Query

Power Query is a powerful data processing tool in Excel that allows you to perform various data manipulation tasks, including splitting text into different columns based on a delimiter.

  1. Select Your Data: Highlight the column that contains the data you want to split.
  2. Open Power Query: Go to the Data tab on the Ribbon and select From Table/Range in the Get & Transform Data group. This will open the Power Query Editor. If your data isn’t already in a table, Excel will prompt you to create one.
  3. Split the Column: In the Power Query Editor, right-click the column you want to split, select Split Column > By Delimiter. Choose the delimiter that your data uses (e.g., space for first and last names) and specify other options based on how you want the data to be split.
  4. Apply & Close: Once you’ve split the column as needed, click the Close & Load button to apply the changes back to your Excel sheet.

Method 4: Using Dynamic Arrays (Excel 365, Excel 2019 and Higher)

If you have Excel 365 or Excel 2019, you can use dynamic arrays to split text directly in the worksheet without needing any special commands or macros.
Split First and Last Name in Excel – Function and Shortcut

Use the TEXTSPLIT Function: This is a direct approach where you can use a formula that leverages the TEXTSPLIT function to split text based on a delimiter and return the results in adjacent cells. For example, if your data is in A2, you could use =TEXTSPLIT(A2, " ") to split the text at each space. The result spills over into the neighboring cells.

Method 5: Using Excel Functions (CONCAT, MID, FIND)

For more complex splitting scenarios, such as when you have names with middle names or multiple last names, combining Excel functions like MID, FIND, and CONCAT can offer more precise control over how text is split.

Complex Splitting Formulas: By using FIND to locate the position of a delimiter and MID to extract text based on those positions, you can tailor your text splitting to accommodate various patterns. This method requires a deeper understanding of Excel functions but is highly flexible.

Method 6: Text to Columns with Advanced Features

While the basic Text to Columns feature is straightforward, exploring its advanced options can help with more complex data. For instance, you can use it to handle cases where some cells contain different numbers of names or where delimiters vary within the same column.

Fixed Width: Apart from delimiters, Text to Columns also offers a Fixed Width option, where you can split text based on character count instead of a delimiter. This is especially useful for data with a consistent format but no clear delimiter.

Method 7: Splitting First Name and Last Name Using VBA

To split text in Excel using VBA (Visual Basic for Applications), you can write a simple macro that takes a string from one cell, splits it based on a specified delimiter (like a space for first and last names), and then places the split parts into separate adjacent cells. Below is a basic example of such a VBA script, which assumes you’re working with full names and want to split these into first and last names. The code also includes comments to help you understand each part of the script.

Before running the VBA script, ensure you have your data in a specific column (for example, column A) and you have sufficient space in the adjacent columns to place the split text. This script starts processing from row 2 (assuming row 1 has headers) and goes down until it encounters an empty cell.

Sub SplitNames()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim fullName As String
    Dim splitName() As String
    
    ' Set the worksheet to your current worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    
    ' Define the range to loop over - here, it's set to column A from row 2 downwards
    Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' Loop through each cell in the defined range
    For Each cell In rng
        If cell.Value <> "" Then ' Check if the cell is not empty
            fullName = cell.Value ' Get the full name from the cell
            splitName = Split(fullName, " ") ' Split the name by space
            
            ' Check if name was split into at least two parts
            If UBound(splitName) >= 1 Then
                cell.Offset(0, 1).Value = splitName(0) ' First name into next column
                cell.Offset(0, 2).Value = splitName(UBound(splitName)) ' Last name into the column after next
            End If
        Else
            Exit For ' Exit the loop if an empty cell is found
        End If
    Next cell
End Sub

How to Use This VBA Script in Excel

  1. Open the VBA Editor: Press Alt + F11 to open the Visual Basic for Applications Editor.
  2. Insert a New Module: In the VBA Editor, right-click on any of the objects for your workbook in the Project Explorer on the left side. Choose Insert > Module to create a new module.
  3. Paste the Code: Copy the provided VBA code and paste it into the module window.
  4. Close the VBA Editor: Close the editor and return to Excel.
  5. Run the Macro: Press Alt + F8, select SplitNames from the list of macros, and then click Run.

This script splits the full name into first and last names based on the first and last space encountered, placing the first name in the column directly to the right of the full name and the last name in the next column over. It’s a basic example; depending on your data, you might need to modify the script, especially if dealing with middle names, titles, or suffixes.

 

Download the Example File

To help you get started with splitting first and last names in Excel, we’ve prepared an example Excel file that demonstrates all seven methods covered in this blog post. This practical resource allows you to see each technique in action and experiment with them on a ready-made dataset.

Download Here:

Excel Name Splitting Techniques Example File

What’s Inside the File?

  • Sheet 1: Formula to Split Names – Explore various Excel formulas in use, including LEFT, RIGHT, MID, FIND, and the dynamic TEXTSPLIT function.
  • Sheet 2: TEXTSPLIT Function – An overview of using the TEXTSPLIT function in Excel for Microsoft 365, Example to Split using Space separated Names.
  • Sheet 3: TEXTSPLIT Function – Another Example to Split using comma separated Names.
  • Sheet 4: Shortcut / Flash Fill – See how Flash Fill can automatically recognize patterns and split names for you using Excel Shortcut.
  • Sheet 5: Text to Columns – A walkthrough of using the Text to Columns feature for different types of data.

How to Use the File:

  1. Download and Open: Click on the link above to download the Excel file, then open it in Excel.
  2. Explore Each Method: Navigate through each sheet to see the methods demonstrated with example data.
  3. Try It Yourself: Use the provided dataset or enter your own data to test out each splitting technique.
  4. Read the Instructions: Each sheet includes step-by-step instructions and tips for using the method effectively.

This example file is designed to be a hands-on companion to this guide, enabling you to quickly apply what you’ve learned and gain confidence in splitting names in Excel.

Notes:

Choosing the right method depends on your specific needs, the complexity of the data, and your familiarity with Excel’s tools. Whether you prefer the simplicity of Flash Fill, the control of Text to Columns, the power of Power Query, the versatility of dynamic arrays and Excel functions, or the customization potential of VBA, Excel provides a rich set of tools for managing and manipulating text data.

Conclusion: Mastering Name Splitting in Excel

We’ve journeyed through seven powerful methods to split first and last names in Excel, each offering unique advantages tailored to different needs and preferences. From the simplicity of Flash Fill to the dynamic capabilities of the TEXTSPLIT function, and even diving into the realms of VBA for custom solutions, Excel provides a versatile toolkit for managing and organizing your data efficiently.

The provided example Excel file showcases each method in action, giving you a hands-on template to explore and apply these techniques in your own work. Whether you’re preparing mailing lists, organizing contact information, or simply tidying up your data for analysis, mastering these methods can significantly enhance your productivity and data management skills.

Remember, the choice of method depends on the specific requirements of your task, the complexity of your data, and your comfort level with Excel’s features. Some methods, like using formulas or the Text to Columns feature, are quick and straightforward, ideal for on-the-fly data manipulation. Others, like Power Query or VBA, offer deeper customization and automation for more complex data processing needs.

As you become more familiar with these tools, you’ll find that splitting names is just the tip of the iceberg. Excel’s vast array of functions and features opens up endless possibilities for data manipulation, analysis, and presentation. We encourage you to experiment with these methods, tweak them to fit your specific needs, and explore beyond to discover even more ways Excel can streamline your workflows and bring your data to life.

Thank you for following along with this guide. We hope it serves as a valuable resource in your Excel endeavors, empowering you to handle your data with confidence and creativity. Happy splitting!

This conclusion wraps up your blog post by summarizing the content, encouraging further exploration, and leaving your readers with a sense of empowerment and curiosity to explore Excel’s capabilities further.

Split First and Last Name in Excel

Share This Story, Choose Your Platform!

Leave A Comment