Do you know how to separate the first name and last name from the full name in Excel? There are multiple ways using which you can separate first and last names in Excel.
Let us now unlock this technique.
Suppose you have a list of names in an excel cell (consisting of first name and last name) and you want that the first name and the last name should be separated such that they are in two different cells as shown in the screenshot below :
There are more than one methods which can be used to separate the first and last name from full name. You can use any of the below methods to achieve the same :
- “Find and Replace” functionality
- “Text to Column” functionality
- Using an excel formula
Let us understand each of these methods one by one :
Get First Name Using “Find and Replace”
The “Find and Replace” is one of the easiest methods which can be used to separate the first name and last name from name in excel.
This is totally a non-formula approach. No formula is required to perform this activity.
So, let us now begin with this technique :
Enter the below names in column A as shown in the screenshot below :
Now, follow the below procedure step by step.
Copy the names from Column A and paste it into column B and column C as shown in the screenshot below :
Now select the names in column B as shown below (Do not select the header row)
Now press Ctrl+H on your keyboard to open the “Find and Replace” dialog box.
In the “Find What” section, press space on your keyboard followed by an asterisk (*) symbol. Keep the “Replace With” section as blank. Do not enter anything over there. Refer to the screenshot below :
The above entries tell Excel to keep the first name and remove all the text after the first name. This means Excel would remove the space and all text after space.
Click on the “Replace All” button. You can see that the last name is removed and you only have the first name in column B.
Also Read: How to Sort by Last Name in Excel
Separate Last Name Using “Find and Replace”
Follow the undermentioned steps.
Select the names in column C as shown in the screenshot below (Do not select the header row)
Now press Ctrl+H on your keyboard to open the “Find and Replace” dialog box.
In the “Find What” section, enter the asterisk symbol (*) followed by a space. Keep the “Replace With” section as blank. Do not enter anything over there.
This denotes that excel should keep the last name and delete all the text before it.
Click on the “Replace All” button. You can now see that the first name is removed and you only have the last names in Column C.
This brings us to the end of the first method. Now let us now learn another method of separating the first name and last name from the full name.
Separate Names Using “Text to Column”
This is also a non-formula based approach. In this approach, we would be using the “Text to Column” Wizard functionality of excel to separate the first and last names from the full name.
Enter the below names in column A as shown in the screenshot below :
Follow the steps specified under:
Step 1 : Select the list of names in column A (Do not select the header) as shown in the screenshot below :
Step 2 : Now go to the “Data” tab. Under the group, “Data Tools”, click on “Text to Columns” button as shown in the screenshot below :
The “Convert Text To Column Wizard – Step 1 of 3” Dialog box would open.
Click on the radio button “Delimited” and click on “Next” button as shown in the screenshot below :
Step 3 : In the next step of this wizard, click on the checkbox “Space” and click on the “Next” button to get into the last step of this wizard :
Step 4 : In the last step of this Excel wizard, select the destination cell in the “Destination” section.
In our case, the destination cell where the resultant names should get extracted is cell B3.
And click on the “Finish” button.
You can now see that the full name got segregated into the first name and last name in column B and column C respectively.
Now let us learn the third method of separating the first and the last name from the full name.
The third method is a formula based approach.
Separate First Name Using LEFT Formula
In this method, a compound formula would be used to separate the first and the last name from the full name in excel.
To get the first name, enter the below formula in cell B3 :
=LEFT(A3,FIND(” “,A3))
As a result, you can see that Excel extracted the first name from the full name in cell B3.
Copy this formula to other cells in column B.
Explanation: We have used =LEFT() formula to get the first name. This formula pulls characters from the left of the text in a cell.
The first attribute of this formula is the text (from which the characters are to be extracted). In our case, it is cell A3 (i.e. the full name).
The second attribute of this formula is the number of characters up to which the text has to be extracted. We would use the =FIND() formula for the same. =FIND() formula finds the position of “ “ (i.e. space) in the full name.
Thus, in a nutshell, the =LEFT() formula in conjunction with =FIND() formula would extract the characters from the left of a text up to the place where it finds “ “ (i.e. space) in the word.
Separate Last Name Using RIGHT Formula
Now in order to get the last name, enter the below formula in cell C3 :
=RIGHT(A3,LEN(A3)-FIND(” “,A3))
As a result, you can see that the excel extracted the last name from the full name in cell C3.
Copy this formula to other cells in column C.
Explanation: We have used =RIGHT() formula to get the last name. It pulls characters from the right of a text in a cell.
The first attribute of this formula is the text (from which the characters are to be extracted). In our case, it is cell A3 (i.e. the full name).
The second attribute of this formula is the number of characters up to which the text is to be extracted. LEN(A3)-FIND(” “,A3) has been used as the second attribute in the main formula =RIGHT(). The LEN(A3) in this nested formula finds the total number of characters in cell A3. And FIND(“ “,A3) finds the position of “ “ (i.e. space) in the text in cell A3.
Reducing the total number of characters by the position of “ “ in the text A3, we get the number of characters from the right up to which the text has to be extracted from the right of the text.