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

In Microsoft 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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Suitable Methods to Separate First and Last Name with Space Using Formula in Excel

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


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-
=LEFT(C5,SEARCH(" ",C5)-1)

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.

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

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.

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

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

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

Step 3:

  • Choose a cell (F5).
  • Apply the formula-
=RIGHT(C5,LEN(C5)-SEARCH(" ",C5,1))

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.

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

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.

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

  • 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 (3 Methods)


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-
=RIGHT(C5, LEN(C5) - SEARCH(" ", C5))

Divide First and Last Name with Comma Using Excel Formula

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.

Divide First and Last Name with Comma Using Excel Formula

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

Divide First and Last Name with Comma Using Excel Formula

Step 3:

  • Choose cell (F5).
  • Apply the formula-
=LEFT(C5, SEARCH(" ", C5) - 2)

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 splitted in different columns.

Divide First and Last Name with Comma Using Excel Formula

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 Suitable Ways)


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-
=LEFT(C5, SEARCH(" ",C5,1)-1)

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

Step 2:

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

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

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

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

Step 3:

  • For the middle name select a cell (F5).
  • Apply the formula-
=MID(C5,SEARCH(" ",C5) + 1, SEARCH(" ", C5, SEARCH(" ", C5) + 1) - SEARCH(" ", C5) -1)

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”.

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

  • You will find all the middle names.

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

Step 5:

  • Choose a cell (G5).
  • Apply the formula-
=RIGHT(C5, LEN(C5) - SEARCH(" ", C5, SEARCH(" ", C5, 1)+1))

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

Step 6:

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

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

  • 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 (4 Easy Methods)


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.

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

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo