How to split first and last names into 2 columns in Excel

How to split first and last names into 2 columns in Excel Picture 1

Sometimes, you may need to split a First and Last column into two Last and First columns to serve different purposes, for example, to sort your list by Alpha B.

Thuthuatphanmem.vn will guide you how to split names quickly and professionally.

How to split first and last names into 2 columns in Excel Picture 2

1. Split names in Excel

There are two ways to separate names in Excel, each with different advantages. The first way is to split the name with Replace, with this way you can split the name quickly and simply. The second way is by functions, this is harder to remember than the first but in return it is better than the first in that you can modify the First and Last name column freely without having to worry about finding and editing more in the First or Last column.

1.1. Split names in Excel by using Replace

This can be considered as the simplest way. The first step is to copy the First and Last name column to the First Name column. Can use the mouse to select the Copy / Paste , or use the key combination Ctrl + C and Ctrl + V .

How to split first and last names into 2 columns in Excel Picture 3

Keep the array highlighted, the data in the name column you just copied.

In the Home ribbon , in the Editting section on the far right corner, choose Find & Select . Finally select the second line Replace .

Or you can use the key combination Ctrl + H .

How to split first and last names into 2 columns in Excel Picture 4

Find and Replace window will appear, enter the two characters '*' (asterisks and spaces) in the Find what box .

How to split first and last names into 2 columns in Excel Picture 5

Click Replace All to start removing the First and Last names leaving the First name.

(Note: This command will only retain the last character cluster after the space, so if your source data fails due to missing spaces, the output data will also be corrupted.)

After the Replace is complete, a message board will appear telling you how many characters you have deleted (the last name and middle name), just click OK to turn it off.

How to split first and last names into 2 columns in Excel Picture 6

1.2. Split names in Excel by combining function types

With method 1, you can split the name column into a fixed column, but when you need to change the data in the First name column (source data), you will have to change the data in the Name column (result data). ). Thuthuatphanmem.vn will share for you more ways to separate names by function, so when changing the data in the First name column, the data in the Name column will automatically change.

How to split first and last names into 2 columns in Excel Picture 7

The functions needed here are the RIGT function, the LEN function, the FIND function, and the SUBSTITUTE function .

- RIGHT function helps to get the right character in a text string.

- LEN function to measure the length of a text string.

- The FIND function helps you find a text string in another document and return the value as the position of the text string to be found in the search text.

- The SUBSTITUTE function helps replace the old text string with a new text string.

Those functions will be combined as follows:

= RIGHT ( X , LEN ( X ) -FIND ("*", SUBSTITUTE ( X , "", "*", LEN ( X ) -LEN (SUBSTITUTE ( X , "", "")))))

Where X is the cell containing the source data (the cell containing the first and last name to split).

After you have finished using the above function string to split the name of a cell, you only need to copy the formula to the lines below.

2. Separate Last Name in Excel

After separating the names, we can separate the last name into the other column.

We use the LEFT and LEN functions to separate the Last Name. Inside:

- LEFT function helps to get the left character in a text string.

- LEN function to measure the length of a text string.

We combine these two functions with the following formula:

= LEFT ( Cell with Full Name , LEN ( Cell with Full Name ) -LEN ( Cell with First Name )

How to split first and last names into 2 columns in Excel Picture 8

After you finish separating them one cell, you only need to copy the formula to the lines below.

Thank you for following the article of TipsMake.vn on How to split first and last names into two columns in Excel.

I wish you successful implementation!