Separate First and Last Name with Space Using Excel Formula (3 Ways)

In Excel, often we need to separate first and last names. Separating first name and last name can be done with flash fill and formulas. In this tutorial, I am going to show you how to separate first and last name using Excel formula with space.

Separate First and Last Name with Space Using Excel Formula: 3 Ways

In this article, I am going to describe 3 simple methods to separate first and last name with space using excel formulas. Suppose we have a dataset of some full employee names. Now, we will separate their first and last names in different columns.

1. Combine LEN, SEARCH, LEFT, and RIGHT Functions to Separate First and Last Name with Space

Separating first and last names can be achieved with “Text to columns” and “Flash fill” and “Excel Formula”. In this method, we are separating names with the combination of LEN, SEARCH, LEFT, and RIGHT functions.

Step 1:

• Select a cell where the formula will be applied. Here I have selected cell (E5).
• Apply the formula-

Where,

• The SEARCH function looks for a text string within another text string and returns its position.
• The LEFT function extracts a given number of characters from the left side of a given text string.

Step 2:

• Press Enter.
• Here we got our first name separated from the cell (C5).
• Drag down downwards to get the first names in the column.

• Thus we get all the first names separated in a new column.

Now, let’s separate the last name. To separate last names follow the instructions below-

Step 3:

• Choose a cell (F5).
• Apply the formula-

Where,

• The RIGHT function returns a specific number of characters from the right side.
• The LEN function returns the length of a given text string.
• The SEARCH function looks for a text string within another text string and returns its position.

Step 4:

• Click Enter.
• With the help of the formula, you get your last name in the cell.
• Drag down the “Fill handle” to get all the last names.

• This way we can get our desired last names.

• Just by using a formula, we have successfully separated our first and last name with space. It is that easy.

Read More: How to Split Names into Three Columns in Excel

2. Divide First and Last Name from Name with Comma Using Excel Formula

In some datasets, you will find a comma(,) between a name. So, does that mean we can not separate the names? No, it’s not. In this method, I will explain how you can divide first and last names if the dataset has a comma between the names.

Step 1:

• Select a cell to write the formula. Here I have selected cell (E5).
• Write the formula in the cell-

Step 2:

• Press Enter.
• It will show the first name from the cell (C5).
• Drag down to fill the column with all the first names.

• So, we got our first names in the column while the dataset has a comma(,) between all the names.

Step 3:

• Choose cell (F5).
• Apply the formula-

Step 4:

• Click Enter.
• In cell (F5) we have our last name.
• Drag down the “Fill handle” to apply the same formula to the rest of the cells.

• Here we got all the first and last names split into different columns.

The name dataset contained a comma(,) within the names. But with the help of formulas, we are able to divide the names from the column.

Read More: How to Split Names with Comma in Excel

3. Separate First, Last, and Middle Name with Space Using Excel Formula

Many datasets contain first, last, and middle names. Previously we used formulas to separate first and last names. Those won’t work if the dataset has a middle name. In this method, I am describing how you can separate all the names with space using excel formulas.

Step 1:

• I have selected a cell (E5) to get the first name in the cell.
• Apply the formula-

Step 2:

• Now Press Enter.
• Drag down to complete the task.

• The column is filled with first names separating names from the dataset.

Step 3:

• For the middle name select a cell (F5).
• Apply the formula-

Where,

• The MID function extracts a given number of characters from the middle of a supplied text string.

Step 4:

• To get the middle name press Enter.
• Drag down the “Fill Handle”.

• You will find all the middle names.

Step 5:

• Choose a cell (G5).
• Apply the formula-

Step 6:

• Click Enter.
• Drag the “Fill handle” downwards.

• We got our last names separated.

• In this way, you will get all the names separated easily.

Read More: How to Split Names Using Formula in Excel

Things to Remember

• To separate names more quickly you can use the flash fill Usually, it’s enabled by default. If it does not work, click the flash fill button on the Data tab > Data tools group. If it still doesn’t work, then go to File > Options, choose “Advanced”, and select the “Automatically Flash Fill” box.

Download Practice Workbook

Conclusion

In this article, I have covered all the methods to separate first and last names with space using excel formulas. Hope you find it useful. Don’t forget to share your view in the comment section below. Thanks!

Related Articles

• How to Split Names in Excel into Two Columns
• Excel VBA: Split First Name and Last Name
• How to Separate First Name Middle Name and Last Name in Excel Using Formula