Split Name in Excel
To split name in Excel (first name, the middle name and the last name), there are two easy ways – the text to column method and the formula method. In the text to column method, a delimiter character needs to be specified. In the formula method, the LEFT, FIND, RIGHT, and LEN functions are used to split names.
How to Separate Names in Excel?
Let us go through the different methods to split names in Excel.
#1 – Text to Column Method
The following data contains the full names of cricket players across different nations. We want to extract the first name and the last name.
The steps of the text to column method are listed as follows:
- Step 1: Select the “full name” column.
- Step 2: In the Data tab, click on the option “text to columnsText To ColumnsText to columns in excel is used to separate text in different columns based on some delimited or fixed width. This is done either by using a delimiter such as a comma, space or hyphen, or using fixed defined width to separate a text in the adjacent columns.read more.”
- Step 3: The box “convert text to columns wizard” opens.
- Step 4: Select the file type “delimited” and click on “next.”
- Step 5: We need to select the type of delimiter that the data contains. In the “full name” column, a “space” separates the first name and the last name. So, we choose “space” as the delimiter option and click “next.”
- Step 6: Clicking on “next” will open the box “convert text to columns wizard.” Here, we choose the cell in which we want to store the first name and the last name.
- Step 7: Click on “finish” and the names appear in two separate columns.
The names in the sixth and the seventh row have been split into three parts–first name, middle name, and last name. The third name is extracted to the extra column.
#2 – Formula Method
The names can also be separated with the help of Excel formulas. For this, we use the LEFT, FIND, RIGHT, and LEN functions.
The steps of the formula method are listed as follows:
- Step 1: Open the LEFT function in cell B2. The LEFT function of ExcelLEFT Function Of ExcelThe left function returns the number of characters from the start of the string. For example, if we use this function as =LEFT ( "ANAND",2), the result will be AN.read more helps extract characters from the left side of the “full name” column.
- Step 2: For the “text” argument of the LEFT function, we choose the cell A2. The “text” is the string from which we want to extract values.
- Step 3: We need to specify the number of characters to be extracted from the left side of the selected text. Thus, from the name “Virat Kohli,” we want to extract five characters to obtain the first name.
- Step 4: The LEFT formula gives the first name “Virat.”
The last name “Kohli” also has five characters. However, the subsequent names have different characters. Hence, we need to supply the numbers manually to extract text from the left side.
An alternative approach is to apply the FIND function.
- Step 5: Open the FIND function to find the first space character in the full name.
Note: The FIND functionFIND FunctionFind function in excel finds the location of a character or a substring in a text string. In other words it finds the occurrence of a text in another text, as it gives us the position, the output returned by this function is an integer.read more returns the position of a specified character within the supplied text string.
- Step 6: In the first argument of the FIND function, we find the space character by entering a blank between a pair of inverted commas.
- Step 7: In the argument “within_text,” we specify the cell reference in which “space” has to be found. So, we select cell A2.
- Step 8: In the argument “start_num,” we specify the position of the character from where the search will begin. So, we enter 1 here.
- Step 9: The formula returns 6, which is the position of the first space character in cell A2. With the help of the FIND function, we can find the number of characters to be extracted from the left side of the text string.
- Step 10: We enter the complete formula “=LEFT(A2,FIND(“ ”,A2,1)-1) to obtain the first names from the “full name” column.
Note: We subtract 1 from the FIND function. Since we do not require the space character in the first name, we reduce the number of characters by one.
- Step 12: Enter the formula “=RIGHT(A2,LEN(A2)-FIND(“ ”,A2,1)).” The names appear in two separate columns, as shown in the succeeding image.
Since we do not know the number of characters to be extracted as the last name, we use the FIND and LEN of ExcelLEN Of ExcelThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more as supporting functions.
Note 1: The LEN function returns the number of characters in the text string. To extract the last name after space, we ignore the position of the space from the total number of characters.
Note 2: In the case of a middle name, the formula extracts the middle and the last name as the “last name.” It is complicated to extract the middle name using the formula method.
Frequently Asked Questions
The formula to extract the first name is stated as follows:
“=RIGHT(cell,LEN(cell)-SEARCH(” “,cell))”
The formula to extract the last name is stated as follows:
“=LEFT(cell,SEARCH(” “, cell)-2)”
“Cell” is the cell reference containing the “full name.”
Note: The SEARCH function determines the position of the space character. We subtract 2 from the LEFT formula because it accounts for two characters–a comma and space.
The subsequent formulas are applied when the names are in the format–first name, middle name, and last name.
The formula to extract the first name is stated as follows:
“=LEFT(cell,SEARCH(” “,cell)-1)”
The formula to extract the middle name is stated as follows:
“=MID(cell,SEARCH(” “,cell)+1,SEARCH(” “,cell,SEARCH(” “,cell)+1)-SEARCH(” “,cell)-1)”
The formula to extract the last name is stated as follows:
“=RIGHT(cell,LEN(cell)-SEARCH(” “,cell,SEARCH(” “,cell,1)+1))”
“Cell” is the cell reference containing the “full name.”
The steps to separate names with the help of flash fill are listed as follows:
– Add the “first name” column next to the “full name” column.
– In the first row, enter the name in the “first name” column that you want to extract.
– In the second row, enter the name in the second cell of the “first name” column.
– Excel senses the pattern and populates the first names in all cells automatically.
Press the “Enter” key.
Note: If the flash fill feature is not enabled, click the “flash fill” button in the Data Tools group (in the Data tab).
Recommended Articles
This has been a guide to Excel Split Name. Here we learn how to separate names in Excel using two methods 1) Text to column method and 2) Formula method with detailed examples and downloadable Excel templates. You may learn more about Excel from the following articles –
- How to Split Cells in Excel?
- Divide a Cell in Excel
- Split Function in VBA
- Split String into Array using VBA
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
47.1K subscribers