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.
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 .
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.
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.
Then you copy down the other cells to separate the buffer family of the corresponding cells.
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 .
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 .
- 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.
Once completed you will receive a small message from Microsoft Excel, click OK to complete. Your results will be as follows:
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 .
Step 2: Next, select Insert -> Module to open the code input window.
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.
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.
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 .
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.
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 .
With other names you do the same or copy down the remaining cells, the result you will separate all names.
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!
You should read it
- How to separate sheets into separate Excel files
- How to separate thousands by commas in Excel
- 2 ways to separate column Full and Name in Excel
- How to separate email from information in Excel
- How to separate the date, month, and year columns into 3 different columns in Excel
- Instructions on how to create diagonal lines in Excel box
- How to separate text strings by commas or spaces in Excel
- How to Convert Word to Excel
May be interested
- How to separate text strings by commas or spaces in Excelseparating 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 separatorin 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 Excelif 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 Excelin 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 Excelyou 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 Excelto separate negative and positive numbers in excel at the same data table, we will use the if function.
- Instructions for separating column content in Excelto 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. 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 Excelin 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?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,...