Separate first and last name in Excel

When entering the data, you have to enter the full name of everyone in the same box, but then you need to separate the middle name and middle name into two different cells to facilitate management. Excel supports you so you can separate first and last name without re-entering data.

Here are three ways you can easily separate first and last names in Excel.

Method 1: Use functions

Step 1: Create the First name column and First name column to store the content after separation. You can format as you like.

Separate first and last name in Excel Picture 1

Step 2: Separate names from the First and Last names column into the First Name column :

In the first cell (E5) need to split the name you enter the function = RIGHT ( C5 ; LEN ( C5 ) -FIND ("*"; SUBSTITUTE ( C5 ; ""; "*"; LEN ( C5 ) -LEN (SUBSTITUTE ( C5 ; ""; "")))))) with C5 as the corresponding Full name cell .

Separate first and last name in Excel Picture 2

Then you copy down the cell by moving the mouse pointer to the lower right corner of the cell and drag down to all the names in the separated list.

Separate first and last name in Excel Picture 3

Step 3: Separating them from the column name into the column Name Buffer :

In the first cell to separate (D5), you enter the formula = LEFT ( C5 ; LEN ( C5 ) -LEN ( E5 ))

- Where: C5 is the full name box; E5 is the split name box.

Separate first and last name in Excel Picture 4

Then you copy down the other cells to separate the buffer family of the corresponding cells.

Separate first and last name in Excel Picture 5

Method 2: Use the Replace dialog box

Step 1: Create the First name column and the First name column to store the data after separation, next you copy the First name column to the Name column .

Separate first and last name in Excel Picture 6

Step 2: Separate names in the Name column .

- Select (black out) data in the Name column , in the Home tab you select Find & Select -> Replace (or Ctrl + H key combination ) to open the Find and Replace dialog box .

Separate first and last name in Excel Picture 7

- In the Replace tab of the Find and Replace dialog box, enter * _ (_ is the space) in the Find what box . Then click Replace All to separate the names.

Separate first and last name in Excel Picture 8

Once completed you will receive a small message from Microsoft Excel, click OK to complete. Your results will be as follows:

Separate first and last name in Excel Picture 9

Step 3: Separating them in the column name in the column Name buffer .

In this step you do the same as Step 2 in Method 1 .

Method 3: Use VBA

Step 1: In Excel file you need to separate first and last name, select Developer tab -> Visual Basic (or combination of Alt + F11 ) to open Microsoft Visual Basic window .

Separate first and last name in Excel Picture 10

Step 2: Next, select Insert -> Module to open the code input window.

Separate first and last name in Excel Picture 11

Step 3: Copy the code below and paste it into the code entry window you just opened.

Private Function TACH (ten As String, lg As Integer)
Dim j As Integer
Name = Trim (ten)
For j = Len (Name) To 1 Step -1
If Mid (Name, j, 1) = "" Then
If lg = "1" Then
TACH = Right (Name, Len (Name) - j)
Else
TACH = Left (Name, j)
End If
Exit For
End If
Next
End Function

Step 4: Save the code file by pressing Ctrl + S or clicking the Save icon , a dialog box appears informing you press Yes to save the file.

Separate first and last name in Excel Picture 12

Step 5: Return to the Excel worksheet, first create more columns containing the First name Middle name and the First name column column after separating.

Separate first and last name in Excel Picture 13

Step 6: Separate them and middle name : you put your cursor at a new cell in the column Name Buffer and enter the formula = TACH ( C5 ; 0) with C5 is complete box full name and press Enter .

Separate first and last name in Excel Picture 14

With other middle names you do the same or copy down to the remaining cells, the result you will be able to separate all the middle names.

Separate first and last name in Excel Picture 15

Step 7: Split the name : you put the cursor in the new cell in the First name column and enter the formula = TACH ( C5 ; 1) with C5 is the full name cell and press Enter .

Separate first and last name in Excel Picture 16

With other names you do the same or copy down the remaining cells, the result you will separate all names.

Separate first and last name in Excel Picture 17

So, with three ways of separating first and last name in Excel that the tutorial, you can easily perform separated first and last name. Good luck!