• Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
Home » Office » How to Separate First and Last Names in Excel

How to Separate First and Last Names in Excel

By: Waseem Patwegar

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

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.

Separate First and Last Names Using Text to Columns Wizard

3. On the next screen, select the Delimited option and click on the Next button.

Select Data Type in Excel Text to Columns Wizard

4. On the next screen, select the Space field and click on Next.

Select Delimiters Convert Text to Columns Wizard

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.

Separate First and Last Names Using Text to Columns Wizard

6. Once you click on the Finish button, you will immediately see the Names separated into First and Last Names.

First and Last Names Separated

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.

Separate First, Last Names and Remove Comma Using Text to Columns Wizard

3. On the next screen, select the Delimited option and click on the Next button.

Select Data Type in Excel Text to Columns Wizard

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.

Select Delimiters Convert Text to Columns Wizard

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.

Remove Comma, Separate First and Last Names Using Text to Columns Wizard

6. Once you click on the Finish button, you will immediately see the Names separated into First and Last Names.

Comma Removed, First and Last Names Separated in Excel

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.

Separate First, Middle, Last Names Using Text to Columns Wizard

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.

Separate First, Last Names and Remove Comma Using Text to Columns Wizard

3. Once, you click on Finish, you will immediately see the Name separated into First, Middle and Last Names.

First, Middle and Last Names Separated in Excel

If the Middle Initial is not required, you can delete the column containing Middle Initial.

Related
  • How to Combine Text From Different Cells In Excel
  • How to Merge and Unmerge Cells in Excel

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar



Recent Posts

  • Fix: iPhone Camera Black Screen Problem
  • How to Clear Your Activity History In Windows 11
  • How to Find WiFi Password in Windows 11
  • How to Enable Touch Keyboard in Windows 11/10
  • How to Send Group Email In Gmail App On iPhone




About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved