How To Separate First and Last Name in Excel
In specific instances in your Excel worksheet; where you have full names combined in a column, you would want to separate the first and last name, but you don’t know how.
This tutorial seeks to solve your question using different methods such as: How to separate full names with the text to column feature, how to separate names in Excel with formulas, and other methods.
How to separate first and last names in Excel- with the text to column tool
In instances where your columns of names have an identical model, such as only the first and last name or first, middle and last name, the text to column tool is the least difficult method to solve this.
A series of steps are explained in detail so you will have no difficulty arising from your task. Now, here’s how it works:
1. Select the column you want to separate:
2. Go to the Data menu and select Text to Columns. A data wizard will popup:
3. The first step of the Convert Text to Columns Wizard, choose the Delimited option and Click Next.
4. In the second step, choose the Space option and the Treat consecutive delimiters as one box due to how our example shows how the names are divided by spaces. The Data Preview segment will show how the names are separated. If you are dealing with names separated by a comma, such as Jesse, Denn, and March, just select the Comma and Space boxes, and select the Treat consecutive delimiters as one checkbox.
5. On the third and final step, choose the Column Data Format and Destination and click finish. You are already proficient with the Text to Column tool at this stage.
Do note that the default General is suitable in most instances. If you want to specify the Destination, input the cell of your choice (this will overwrite previous data).
6. Now, you have accomplished this task. Here is the first, middle, and last name being separated into individual columns:
How to Separate first and last names in Excel using formulas
Using formulas to separate names is a much better option. It presents an active solution in updating the names instinctively. You have learned the Text to Column tool, but the use of formulas for splitting up names serves a better worth.
How to separate first and last name from full name with space
Formulas aid in situations where the first and last names are separated by space in the same column.
The formula for the name on the left (first name)
The first name can be withdrawn by using this collective formula:
=LEFT(CELL,SEARCH(” “,CELL)-1)
Here’s how to use it:
- Create a column beside the first ‘Full Name’ Column. It is titled ‘First Name’ in my example.
- Input the formula ‘LEFT(A2,SEARCH(” “,A2)-1)’ in cell B2.
- Rather than input the formula manually in each cell, just select the sizing handle, then drag it down.
- This will copy the formula from that cell to the others.
The SEARCH or FIND function can be used to get the location of the space character(“ “) and subtract the number of the space to remove the space. This number is delivered to the LEFT function as the number of characters to be withdrawn from the right string.
The formula for the name on the right (last name)
The last name can be withdrawn by using this collective formula:
=RIGHT(CELL,LEN(A2)-SEARCH(” “,CELL,1))
Here’s how the formula is used:
- Create a column beside the ‘First Name’ column. In my example, I titled it ‘Last Name.’ This is the third column.
- Input the formula ‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))
- Rather than inputting the formula manually in each cell, just select the sizing handle, then drag it down.
- This will copy the formula from that cell to the others.
After following these steps, you will have something similar to this:
In situations where the task you’re working on contains a middle name or initial, the formula gets more complex to withdraw the last name:
=RIGHT(A2,LEN(A2) – SEARCH(“#”, SUBSTITUTE(A2,” “, “#”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”)))))
How to separate first and last name from the full name with a comma
In tasks where you have a column of full names divided into Last name and first name format, you can divide them into individual columns using formulas.
Formula for name on the left (last name)
=LEFT(CELL, SEARCH(” “,CELL) – 2)
It’s exactly like the previous example, where the SEARCH function is used to locate the space, and then 2 is subtracted due to the comma and space characters to obtain the last name.
Here’s how to use it:
- Create a column beside the ‘First Name’ Column. It is titled ‘Last Name’ in my example. This is the third column.
- Include the formula =LEFT(A2, SEARCH(” “,A2) – 2)
- Rather than inputting the formula manually in each cell, just select the sizing handle, then drag it down.
- This will copy the formula from that cell to the others.
Formula for name on the right (first name)
=RIGHT(cell, LEN(cell) – SEARCH(” “, cell))
This is the same as the other example; the SEARCH function is used to locate the space, and it is subtracted from the string length to get the first name.
Here is how it is used:
- Create a column beside the ‘Full Name’ Column. It is titled ‘First Name’ in my example. This is the second column.
- Include the formula =RIGHT(A2, LEN(A2) – SEARCH(” “, A2))
- Rather than inputting the formula manually in each cell, just select the sizing handle, then drag it down.
- This will copy the formula from that cell to the others
How to separate the full name into first, middle, and last name
In separating full names that comprise the first, middle, and last names, specific methods are required based on the format of the names.
Some formulas work for separating names in this order.
For the first name, the SEARCH function is used to determine the location of the space character, and then the formula is used:
=LEFT(A2, SEARCH(” “,A2) – 1)
Similar to the previous example, here’s how it is used:
- Create a column beside the ‘Full Name’ Column. It is titled ‘First Name’ in my example. This is the second column.
- Include the formula =LEFT(A2, SEARCH(” “,A2) – 1)
- Rather than inputting the formula manually in each cell, just select the sizing handle, then drag it down.
- This will copy the formula from that cell to the others.
For the middle name, you have to know the location of the two space characters in the name. To know the location of the first space character, the SEARCH function (“, “ A2) is used, and 1 is added to withdraw the second space character. The length of the middle name is determined by subtracting the location of the first space character from the location of the second character.
Here’s the formula for the middle name:
=MID(A2, SEARCH(“ “,A2) + 1, SEARCH( “ “,A2, SEARCH(“ “,A2)+1) -SEARCH(“ “, A2)-1)
- Create a column beside the ‘First Name’ Column. And title it ‘Middle name.’ This is the third column.
- Include the formula =MID(A2, SEARCH(“ “,A2) + 1, SEARCH( “ “,A2, SEARCH(“ “,A2)+1) -SEARCH(“ “, A2)-1)
- Rather than inputting the formula manually in each cell, just select the sizing handle, then drag it down.
- This will copy the formula from that cell to the others.
For the last name, the location of the second space character is gotten through the SEARCH function to subtract the second space character from the string length. Here’s the formula for the last name:
=RIGHT(A2,LEN(A2)-SEARCH(” “,A2, SEARCH((” “,A2,1)+1))
Here is how it is used:
- Create a column beside the ‘Middle name’ Column and title it ‘Last Name’. This is the fourth column.
- Include the formula = RIGHT(A2,LEN(A2)-SEARCH(” “,A2, SEARCH((” “,A2,1)+1))
- Rather than input the formula manually in each cell, just select the sizing handle, then drag it down.
- This will copy the formula from that cell to the others.
To separate full names of the order, Last name, First name, and Middle name, the formula has a tweak.
For First name:
=MID(A2, SEARCH(“ “,A2) + 1, SEARCH( “ “,A2, SEARCH(“ “,A2)+1) -SEARCH(“ “, A2)-1)
For Middle name:
= RIGHT(A2,LEN(A2)-SEARCH(” “,A2, SEARCH((” “,A2,1)+1))
For Last name:
=LEFT(A2, SEARCH(” “,A2) – 1)
To separate full names of the order; First name, last name, and suffix, this is the correct application of the formula:
For First name:
=LEFT(A2, SEARCH(” “,A2) – 1)
For Last name:
= MID(A2, SEARCH(“ “,A2) + 1, SEARCH( “ “,A2, SEARCH(“ “,A2)+1) -SEARCH(“ “, A2)-1)
For the Suffix:
== RIGHT(A2,LEN(A2)-SEARCH(” “,A2, SEARCH((” “,A2,1)+1))
These various combinations of formulas are used to separate different arrangements of names in Excel. Reversing the formulas can lead to your specified result.
How to Separate Names Using Flash Fill
Excel’s Flash Fill is a tool present in versions of Excel from 2013 to the current version. It is used to supply data of a particular pattern and separate data.
Here’s how the tool works:
1. Create a new column (first name) besides the first column (full name).
2. After creating the column, fill out the column created (first name). Excel will observe a pattern, and its algorithm will automatically fill out the names in other cells.
The Flash Fill tool is already set to work by default. If it isn’t so on your Excel software, head to the File menu, choose the options section, click on Advanced, and select the Automatically Flash Fill Box.
How to Use Split Names Tool
Having learned about the use of the text to column feature, formulas, and the flash fill tool, the next element in separating names is the Split Names Tool. The split names tool proffers a dynamic solution to separating names because it works perfectly with heterogeneous datasets compared to text to column features, formulas, and the flash fill tool. Other features are only for homogenous datasets uses.
For example, if you have a task where the ‘Full name’ column comprises full names arranged in an individual order, such as the first name being first before the last name in one cell and the last name coming first before the first name in another cell. Trying to use these tools; text-to-column feature, formulas, and the flash fill tool will lead to an irregular data structure.
In such cases, the Split Names tool comes in. It works perfectly for heterogeneous datasets. The Split Names tool is a feature of AbleBits Ultimate Suit in Excel. AbleBits created this is a commercial plug-in that provides additional professional features and tools for Excel. It helps with outliers and oddball cases of data such as suffixes and prefixes.
Here are steps on how to separate names with AbleBits Ultimate Suit;
1. Choose any cell which contains the name you want to split and then head to the AbleBits Data tab > Split Names.
Related Articles
2. Highlight the name parts you want to separate and click Split. You can also add desired columns that are specified according to individual patterns.
After doing this, the dataset will be separated into individual data columns with utmost precision. The AbleBits Ultimate Suite is quite efficient, and it works without errors.
Conclusion
This tutorial has full explanation in detail the methods, features, and tools you can use to separate names into first, middle, and last.
However, it isn’t just limited to the separation of names. It also can be used for other data fields when creating spreadsheets. Data fields such as:
- E-mail address
- Phone numbers
- Date of birth
This implies using the information from this tutorial to split data according to your suited task.
Most people use commas, hyphens, and spaces to separate the data in a column, but the above methods will set your spreadsheet apart from every other common one. Now, you can efficiently sort names.
