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!

5 ★ | 1 Vote

May be interested

  • How to separate text strings by commas or spaces in ExcelHow to separate text strings by commas or spaces in Excel
    separating text strings based on commas or spaces is a trick that helps you split the characters in a parameter cell into different clusters based on commas or spaces that parameter cell contains. tipsmake will guide you how to separate text strings by commas or spaces in excel.
  • How to convert commas to dots in Excel - Convert unit separatorHow to convert commas to dots in Excel - Convert unit separator
    in excel, to separate units in data, we can use periods or commas. so how to convert a period to a comma or vice versa?
  • How to Convert Word to ExcelHow to Convert Word to Excel
    if you want to move a list or table of data from word to excel, you don't need to copy and paste each information into a separate cell in the spreadsheet. by properly formatting the word document first, you can easily import the document into excel with just a few clicks.
  • How to convert commas into dots in ExcelHow to convert commas into dots in Excel
    in excel, to separate units in a metric, we can use dots or commas. so how to convert the dot to a comma or vice versa?
  • How to Add a New Tab in ExcelHow to Add a New Tab in Excel
    you can add tabs in excel, called 'worksheets,' to keep your data separate but easy to access and reference. excel starts you with one sheet (three if you're using 2007), but you can add as many additional sheets as you'd like. open your...
  • How to separate negative and positive numbers in ExcelHow to separate negative and positive numbers in Excel
    to separate negative and positive numbers in excel at the same data table, we will use the if function.
  • Instructions for separating column content in ExcelInstructions for separating column content in Excel
    to separate content in an excel field into different columns, we can use two different ways.
  • How to split, merge first name in ExcelHow to split, merge first name in Excel
    how to split, merge first name in excel. normally you enter the list of staff, students, students .... the first name and last name fields are on the same column. however, in the process of working with some software, it is necessary to get the first and last name of the field ..
  • Split numbers from strings in ExcelSplit numbers from strings in Excel
    in the process of working with excel, i have encountered some cases that require separating only the fraction in a given string, now this post shares with you how to do this. the following article details how to separate numbers from a given string in excel.
  • What can the shortcut Ctrl + E in Excel do?What can the shortcut Ctrl + E in Excel do?
    the shortcut ctrl + e in excel is a very familiar shortcut and you can do a lot with this shortcut such as merging data in excel tables, separating data in excel,...