Splitting First Names and Last Names in Excel

Buzz

Content

Method 1: Using a Function

Method 2: Using the Replace Dialog Box

Method 3: Utilizing VBA

When entering data, you may have inputted full names in a single cell, but later need to separate the first name and last name into two different cells for easier management. Excel provides various methods to help you split names without re-entering data.

Below are three ways to easily split first names and last names in Excel.

Method 1: Using a Function

Step 1: Create Columns for Last Name and First Name

Step 2: Split the First Name from the Full Name Column to the First Name Column

In the first cell (E5) where you want to split the names, enter the formula =RIGHT(C5;LEN(C5)-FIND('*';SUBSTITUTE(C5;' ';'*';LEN(C5)-LEN(SUBSTITUTE(C5;' ';''))))) with C5 being the corresponding Full Name cell you want to split.

Then, copy down to the cells below by moving the mouse pointer to the bottom right corner of the cell and dragging down, so all the names in the list are split.

Step 3: Split the Last Name from the Full Name Column to the Middle Name Column

In the first cell where you want to split (D5) the last names, enter the formula =LEFT(C5;LEN(C5)-LEN(E5))

- Where: C5 is the cell containing the full name; E5 is the cell containing the first name just split.

Then, copy down to the other cells to split the last names of the corresponding cells.

Method 2: Using the Replace Dialog Box

Step 1: Create columns for Middle Name and First Name to store the data after splitting, then copy the Full Name column to the First Name column.

Step 2: Split the first name in the First Name column.

- Select (highlight) the data in the First Name column, go to the Home tab, choose Find & Select -> Replace (or press Ctrl + H) to open the Find and Replace dialog box.

Method 3: Utilizing VBA

Step 1: In your Excel file where you need to separate first and last names, navigate to the Developer tab -> Visual Basic (or press Alt + F11) to open the Microsoft Visual Basic window.

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

Step 3: Copy the code snippet below and paste it into the code input 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 will appear, press Yes to save the file.

Step 5: Return to the Excel spreadsheet, first create a column for Last Name and a column for First Name to contain the separated names.

Step 6: Split last names and first names: place the cursor in a new cell in the Last Name column and enter the formula =SPLIT(C5;0) with C5 being the cell containing the full name and press Enter.

For other last names, repeat the process or copy down to the remaining cells, and you will successfully split all last names.

Step 7: Split first names: place the cursor in a new cell in the Last Name column and enter the formula =SPLIT(C5;1) with C5 being the cell containing the full name and press Enter.

For other names, repeat the process or copy down to the remaining cells, and you will successfully split all names.

Thus, with the three methods of splitting last names and first names in Excel as instructed in the article, you can easily perform the separation. Wishing you success!

Frequently Asked Questions

What are the methods to split full names into first and last names in Excel?

There are three main methods to split full names in Excel: using functions to separate names, utilizing the Replace dialog box, and employing VBA code. Each method provides a straightforward approach to managing name data efficiently.

How can I use a function to split names in Excel?

To split names using a function, first create columns for first and last names. Use the formula =RIGHT(C5,LEN(C5)-FIND('*',SUBSTITUTE(C5,' ', '*', LEN(C5)-LEN(SUBSTITUTE(C5,' ',''))))) to extract the first name, and =LEFT(C5,LEN(C5)-LEN(E5)) to find the last name.

Can I separate names in Excel without re-entering the data?

Yes, you can separate names in Excel without re-entering the data by using built-in functions, the Replace dialog box, or VBA scripts, allowing you to manipulate and manage your data effectively.

What steps are involved in using the Replace dialog box for name separation?

To use the Replace dialog box for name separation, create columns for the separated names. Highlight the data, go to the Home tab, select Find & Select, and choose Replace. This opens a dialog where you can specify how to split the names.

What is the VBA method for splitting first and last names in Excel?

To split names using VBA, open the Visual Basic editor, insert a new module, and paste the provided code snippet. You can then use the formula =SPLIT(C5,0) for last names and =SPLIT(C5,1) for first names in your Excel sheet.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]