Buzz
During data editing, there are times when you need to split the Name column into two columns: First Name and Last Name, for various purposes such as sorting lists alphabetically by last name.
Mytour will guide you on the quickest and most professional method to split names.
1. Splitting Names in Excel
There are two methods to split names in Excel, each with different advantages. The first method is splitting names using Replace, where you can quickly and easily split names. The second method involves using functions, which may be harder to remember than the first method, but it allows for easier modification of the Name column without needing to search and edit further in the First Name or Last Name columns.
1.1. Splitting Names in Excel using Replace
This could be considered the simplest method. The first step is to copy the Name column to the Last Name column. You can use right-click and select Copy/Paste or use the keyboard shortcuts Ctrl C and Ctrl V.
Keep the range selected as the data in the copied Last Name column.
In the Home ribbon, under the Editting section at the far right corner, select Find & Select. Finally, choose the second line Replace.
Alternatively, you can use the keyboard shortcut Ctrl H.
The Find and Replace dialog box will appear, where you enter two characters “* ” (asterisk and space) into the Find what box.
Click Replace All to start removing middle names and leave only the Last Name.
(Note: This command will only retain the last set of characters after the space, so if your source data is corrupted due to missing spaces, the output data will be corrupted as well.)
After the Replace operation, a notification dialog will appear indicating how many character sets (including first and middle names) you have removed. Simply click OK to dismiss it.
1.2. Splitting Names in Excel using a combination of functions
With method 1, you can separate the Name into a fixed column, but when you need to change the data in the Name column (source data), you will have to change the data in the Last Name column (result data) as well. Mytour will share another method of splitting names using functions, so when you change the data in the Name column, the data in the Last Name column will automatically change.
The functions needed here include the RIGHT function, the LEN function, the FIND function, and the SUBSTITUTE function.
- The RIGHT function helps retrieve characters from the right side of a text string.
- The LEN function measures the length of a text string.
- The FIND function helps you locate a text string within another text string and returns the position of the text string you are searching for within the text.
- The SUBSTITUTE function assists in replacing old text with new text in a text string.
These functions will be combined as follows:
=RIGHT(X,LEN(X)-FIND('*',SUBSTITUTE(X,' ','*',LEN(X)-LEN(SUBSTITUTE(X,' ','')))))
Where X represents the source data cell (cell containing the full name to be separated).
After using the function chain to separate the name in one cell, simply copy the formula down to the rows below.
2. Splitting Last Name in Excel
Once the name is separated, we can isolate the Last Name into another column.
We utilize the LEFT and LEN functions to extract the Last Name. Where:
- The LEFT function helps extract characters from the left side of a text string.
- The LEN function measures the length of a text string.
We combine these two functions with the following formula:
=LEFT(Cell containing Full Name,LEN(Cell containing Full Name)-LEN(Cell containing Last Name))
After successfully splitting the last name in one cell, simply replicate the formula downwards to subsequent rows.
Thank you for following the tutorial on Mytour about How to split first name and last name into two columns in Excel.
Wishing you all success!
