How to Separate Names and Surnames in Distinct Columns in a Microsoft Excel List

2024 Author: Samantha Chapman | [email protected]. Last modified: 2023-12-17 09:13

You've probably had to work with a list of first and last names written in an Excel spreadsheet before. If first and last names are together in the same cell, you will not be able to put them in alphabetical order according to the last names. First, you will need to separate the first name from the last name. Here is explained below how to do it.

Steps

Step 1. As in the example below, your spreadsheet contains first and last names together in the same cell

Step 2. In this case, hold the cursor over the "B" column header, until a down arrow appears; then click with the left mouse button to select the entire column, as shown below

Step 3. Then select the DATA tab and then the COLUMN TEXT button

Note that after the column you are changing you must have several blank columns. If necessary, highlight the column and insert 2-3 more columns. Otherwise, the conversion will write the data above the data contained in the subsequent columns.

Step 4. In the first window of the Convert Text to Columns Wizard, choose Delimited

The Fixed Width choice is fine if the segments to be separated all have the same width (for example when separating telephone numbers from area codes)

Step 5. In the second window of the Convert Text to Columns Wizard, choose the delimiter, which is what separates what you want to have into separate columns

In our case, it is simply a space, so we will select Space. You can also check "Treat consecutive delimiters as one".

• If the names are separated by commas (for example Rossi, Paolo), then you will have to choose Comma as the delimiter, and so on.

  

  Step 6. In the third window of the Convert Text to Columns Wizard, choose "General" formatting and leave the rest unchanged

  To continue, press the "Finish" button.

  • This area is changed only when dealing with numbers and dates.

    

    Step 7. Check what you have done

    The spreadsheet should look like this.

    

    Step 8. Now you can change the header to Name and Surname and sort by surname if you want

    Here's what the spreadsheet looks like after editing the headings and alphabetizing the surnames.

    Advice

    This can also be done with Excel 2003, not just the latest version

    Warnings

    • ALWAYS make a copy of your spreadsheet and work with that copy rather than the original.
    • REMEMBER to insert extra columns to the right of the column you are changing; otherwise you will write in the columns that already contain other data!