How to Separate First and Last Name in Excel

Imagine you have a table and in one column, you have full names of customers of your company on it.

Here we are going to explain how to separate first and last name from each other and write them in the different columns.

How to Separate the First Name in Excel;

As you see, If you want to use “Left” command because the number of letters in each name can be different this formula can not work correctly and you need to check the list again.

For solving this problem, you can define this formula to calculate the number of letters for each name.

Because we have one space between the name and last name we can use from this opportunity and write this formula;

FIND(" “,B4,1)

If we had “,” between first name and last name in our table we could change this formula to

FIND(”,",B4,1)

Now we can calculate the number of characters for each name, and we can modify last formula to this formula to find names correctly.

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

Now we should specify this formula to other cells of the column.

How to Separate the Last Name in Excel;

As you see If we want to use the “Right” formula for that, due to the number of characters of each last name are different, this formula doesn’t work correctly.

For solving this problem we can find the number of each word separately and then use from “Right” formula.

For that you can use this formula;

LEN(B4)-FIND(” “,B4,1)

then we can modify the first formula to this;

=RIGHT(LEN(B4)-FIND(” “,B4,1))

Specify this formula to other cells of the column.

You can see, the values of the Last names were shown correctly.