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

Get FREE Advanced Excel Exercises with Solutions!

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


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 the 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


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 split into 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. 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

2 Comments
  1. Good day,

    Can you help me on how to do this?

    Mhar J. Javate to Javate, Mhar J. arrange in one column in excel.

    Thanks

    • Reply Avatar photo
      Fahim Shahriyar Dipto Dec 28, 2022 at 3:30 PM

      Hello Javate,
      Thanks for commenting. Your query is similar to reverse names. You can do that in one column. For this, go to cell C5 and insert the following formula.
      =MID(B5&","&B5,SEARCH(".",B5)+1,LEN(B5)+1)
      You have to use the MID, SEARCH, and LEN functions combinedly. You will get the output like the image below after pressing ENTER.

      You can follow the Reverse Names in Excel article to get the proper idea also.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo