We have a dataset of some full employee names. We will separate their first and last names in different columns.
Method 1 – Combine LEN, SEARCH, LEFT, and RIGHT Functions to Separate the First and Last Name with a Space
Steps:
- Select a cell where the formula will be applied. We have selected E5.
- Apply this formula:
=LEFT(C5,SEARCH(" ",C5)-1)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.
- Press Enter. We got our first name separated from the cell (C5).
- Drag the fill handle down to get the first names in the column.
- We get all the first names separated in a new column.
- Go to cell F5.
- Apply the formula:
=RIGHT(C5,LEN(C5)-SEARCH(" ",C5,1))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.
- Click Enter.
- Drag down the Fill handle to get all the last names.
- We get our desired last names.
Here’s the result.
Read More: How to Split Names into Three Columns in Excel
Method 2 – Divide the First and Last Name from the Name with a Comma Using a Formula
Steps:
- Select a cell to write the formula. We have selected E5.
- Insert the following:
=RIGHT(C5, LEN(C5) - SEARCH(" ", C5))
- Press Enter.
- Drag down to fill the column with all the first names.
- We got our first names in the column while the dataset has a comma (,) between all the names.
- Choose cell F5.
- Apply the formula:
=LEFT(C5, SEARCH(" ", C5) - 2)
- Hit Enter.
- Drag down the Fill handle to apply the same formula to the rest of the cells.
- We got all the first and last names split into different columns.
Here are the results.
Read More: How to Split Names with Comma in Excel
Method 3 – Separate the First, Last, and Middle Name with a Space Using Excel Formula
Steps:
- We have selected a cell (E5) to get the first name in the cell.
- Apply the formula:
=LEFT(C5, SEARCH(" ",C5,1)-1)
- Press Enter.
- Drag the Fill Handle down.
- The column is filled with first names separating names from the dataset.
- For the middle name, select cell F5.
- Apply the formula:
=MID(C5,SEARCH(" ",C5) + 1, SEARCH(" ", C5, SEARCH(" ", C5) + 1) - SEARCH(" ", C5) -1)The MID function extracts a given number of characters from the middle of a supplied text string.
- Press Enter.
- Drag down the Fill Handle.
- You will find all the middle names.
- Choose cell G5.
- Apply the formula:
=RIGHT(C5, LEN(C5) - SEARCH(" ", C5, SEARCH(" ", C5, 1)+1))
- Click Enter.
- Drag the Fill handle down.
- We got our last names separated.
Here are the results.
Read More: How to Split Names Using Formula in Excel
Things to Remember
- You can also use Flash Fill, but you’ll need to enter a few values manually and the function isn’t dynamic.
Download the Practice Workbook
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
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
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.