How to Split First And Last Name in Excel (6 Easy Ways)

The sample dataset contains two columns: Full Name and Account Number. We’ll split the Full Name column.

Split the First and Last Name in Excel: 6 Easy Ways

Method 1 – Using Text to Columns to Split the First and Last Name

Select the cell or cell range you want to split. We selected the cell range B4:B13.
Open the Data tab and select Text to Columns.

A dialog box will pop up.
In Choose the file type that best describes your data, select Delimited as the data has a space character between the first and the last name.
Click Next.

Another dialog box will pop up. Select the Delimiters that your data has. We checked Space.
Click Next.

Another dialog box will pop up. Select the Destination to place your split data.

We selected the D4 cell to place the separated first and last names.

Click Finish.

A warning message will appear.
Click OK.

You will get the first and last names from the full names.

Method 2 – Using Flash Fill to Split the First and Last Name

We added two new columns for First Name and Last Name.
Type the name part of the Full Name that you want to extract in the first cell. We typed the name Adam in the First Name column.

In the second cell, type the first name from the B5 cell. In most cases, Excel will detect a pattern and populate the first names in all other cells automatically.

Press Enter to Fill all the First Names automatically.

Repeat the process for extracting the Last Name from the cells with Full Name.

Here’s our result.

In case your Flash Fill feature is not enabled by default, click on the Data tab and select Flash Fill.

If it still doesn’t work, go to Options, select Advanced and check the Flash Fill box, then make sure the Automatically is selected under Editing options.

Method 3 – Using Flash Fill to Split First and Last Names When a Middle Name Exists

We’ve added middle names to the cells in the Full Name column.

We added two new columns one for First Name another for Last Name.
Type the name part of the Full Name that you want to extract in the first cell. We typed the name Adam in the First Name column.

Select the cell where you put the pattern to follow. We selected cell D4.
Open the Data tab and select Flash Fill in Data Tools.

You will get the First Name from the Full Name.

Repeat the process for Last Name after inserting the first value manually for cell E4.

You will get the Last Names from the Full Name column.

Method 4 – Using Functions to Split the First and Last Name

Case 4.1 – Using LEFT and FIND Functions to Split the First Name

Select any cell to place the First Name.
Insert the following formula.

=LEFT(B4,FIND(" ",B4,1)-1)

Formula Breakdown

➦ FIND(” “,B4,1)—> will find the position of the first space character.
• Output: 5

➦ FIND(” “,B4,1)-1 —> becomes
• 5-1
• Output: 4

➥ LEFT(B4,FIND(” “,B4,1)-1)—> This will return the First Name from the Full Name column.
• LEFT(B4, 4)
• Output: Adam
• Explanation: Extracts the First 4 letters from the Full Name.

Press the Enter key.

Use the Fill Handle to AutoFill the formula for the rest of the cells.

Case 4.2 – Using RIGHT and FIND Functions to Split the Last Name

Select any cell to place the Last Name.
Insert the following formula.

=RIGHT(B4,LEN(B4)-FIND(" ",B4,1))

Formula Breakdown

➦ FIND(” “,B4,1)—> will find the position of the first space character.
• Output: 5

➦ LEN(B4)—> will return the number of characters in the text string.
• Output: 10

➥ LEN(B4)-FIND(” “,B4,1) —> becomes
• 10-5
• Output: 5

➨ RIGHT(B4,LEN(B4)-FIND(” “,B4,1))—> This will return the Last Name from the Full Name column.
• RIGHT(B4, 5)
• Output: Smith
• Explanation: Extracted the Last 5 letters from the Full Name.

Hit Enter.

Use the Fill Handle to AutoFill the formula for the rest of the cells.

Method 5 – Using a Function to Split the First and Last Name with a Comma

We’ve modified the dataset to include a comma as a separator.

Case 5.1 – Using LEFT and SEARCH Functions to Split the First Name

Select any cell to place the First Name.
Insert the following formula.

=LEFT(B4,SEARCH(" ",B4)-2)

Formula Breakdown

➦ SEARCH(” “,B4) —> will search the position of the first space character.
• Output: 6

➦ SEARCH(” “,B4)-2 —> becomes
• 6-2
• Output: 4

➥ LEFT(B4,SEARCH(” “,B4)-2)—> This will return the First Name from the Full Name column.
• LEFT(B4, 4)
• Output: Adam
• Explanation: Extracts the First 4 letters from the Full Name.

Hit Enter.

Use the Fill Handle to AutoFill the formula for the rest of the cells.

Case 5.2 – Using RIGHT and SEARCH Functions to Split Last Name

Use the following formula.

=RIGHT(B4, LEN(B4) - SEARCH(" ", B4))

Note that we’re still searching for the space as it’s after the comma.

Formula Breakdown

➦ SEARCH(” “, B4) —> will search the position of the first space character.
• Output: 6

➦ LEN(B4) —> will return the number of characters in the text string.
• Output: 11

➥ LEN(B4) – SEARCH(” “, B4) —> becomes
• 11-6
• Output: 5

➨ RIGHT(B4, LEN(B4) – SEARCH(” “, B4)) —> This will return the Last Name from the Full Name column.
• RIGHT(B4, 5)
• Output: Smith
• Explanation: Extracts the Last 5 letters from the Full Name.