A common task given in an Excel class and also in practical work related situations is to separate First and Last Names. You will find below the steps to separate First and Last Names in Excel.
Separate First and Last Names in Excel
The need to separate First and Last Names in Excel arises due the full names of students or employees being entered in a single Excel column.
This situation also occurs when student or employee data is imported from a Data Entry or Accounting program into Microsoft Excel Format.
When the Names are stored in a single Excel column, they are usually stored as FirstName LastName, LastName, FirstName (comma separating the two) and also as FirstName Midinitial LastName.
While it is possible to Separate First and Last Names using Excel formulas, the easiest way to separate First and Last Names in Excel is to use ‘Text to Column Wizard’ as available in Microsoft Excel.
1. Separate First and Last Names Using Text to Column Wizard
Follow the steps below to separate Fist and Last Names using Text to Columns wizard as available in Microsoft Excel.
1. Open the Excel File containing Names that you want to separate.
2. Select the entire Column containing Names > click on the Data tab in the top menu bar and click on Text to Columns option in “Data Tools” section.
3. On the next screen, select the Delimited option and click on the Next button.
4. On the next screen, select the Space field and click on Next.
5. On the next screen, you can choose the destination for First and Last Names or go with the existing selection and click on the Finish button.
6. Once you click on the Finish button, you will immediately see the Names separated into First and Last Names.
As you can see in above image, the Names have been separated into two different columns.
2. Remove Comma, Separate First and Last Names In Excel
If the names are written in format LastName, FirstName, you can Follow the steps below to remove Comma and separate First, Last Names.
1. Open the Excel File containing Names that you want to separate.
2. Select the entire Column containing combined Names > select the Data tab in the top menu bar and click on Text to Columns option in “Data Tools” section.
3. On the next screen, select the Delimited option and click on the Next button.
4. On the next screen, select Space and Other fields > type “,” (Comma) in the little box next to “Other” and click on the Next button.
5. On the next screen, you can choose the destination for First and Last Names or go with the existing selection and click on the Finish button.
6. Once you click on the Finish button, you will immediately see the Names separated into First and Last Names.
3. Separate First, Middle and Last Names in Excel
If the Names are in format ‘FirstName Midinitial LastName’, the same steps as provided in above two cases can be used to separate First, Middle and Last Names.
1. Select the entire Column containing Names > select the Data tab in the top menu bar and click on Text to Columns option in “Data Tools” section.
2. On the next screen, you can select your preferred destination for First, Middle and Last Names or accept the default destination and click on the Finish button.
3. Once, you click on Finish, you will immediately see the Name separated into First, Middle and Last Names.
If the Middle Initial is not required, you can delete the column containing Middle Initial.
