Data separation in Excel

How to Separate Cell Data in Excel Spreadsheet.

This process has broader applications, but this guide will focus on isolating the first name from a list of recipients. The original Excel spreadsheet is formatted to have “last name, first name”. So, rather than manually editing each cell to remove the last name, here’s how to separate the data and create a new column with only the first name.

 

  1. Open the Excel document with the data you want to separate. Highlight the column with the data you’re going to separate. From the ribbon at the top, select Data>Text to Columns.
  2. Delimited>Next.
  3. Check Comma and Space>Next.
  4. Select the undesired column of data in the preview box (it will be highlighted in black) and select Do not import column (skip). Note the example skips the last name and the middle initial, and leaves the first name on General. The last name and middle initial will be discarded, leaving only the first name.
  5. Select the row you want to keep (highlighted in black) and change its destination (otherwise you will replace the original column you highlighted in step #1). For example, if your list of names is in column B, then change the letter B to the letter of a blank column. E.G. $B$1 to $D$1. Click Finish.
  6. Give the new row a title so that you can find it if you’re using these names for something like mail merge.

Open the Excel document with the data you want to separate. Highlight the column with the data you’re going to separate. From the ribbon at the top, select Data>Text to Columns.

Data>Text to Columns

Delimited>Next.

Delimited>Next

Check Comma and Space>Next.

Select Comma and Space then click Next

Click on the undesired column of data in the preview box (selected sections are highlighted in black) and select Do not import column (skip). Note that the example skips the last name and the middle initial and leaves the first name on General. The last name and middle initial will be discarded leaving only the first name.

Do not import column (skip)

Select the row you want to keep (highlighted in black) and change its destination (otherwise you will replace the original column you highlighted in step #1). The example destination changes from column $B$1 to $D$1. D is a blank column in the example. Enter whatever letter doesn't already have data in your spreadsheet. Click Finish.

Destination

Give the new column a title so that you can find it if you're using these names for something like mail merge.

Name the column First Name Only

 

If you would like assistance, please feel free to contact the Binghamton University ITS Help Desk at 607-777-6420 or Submit a Request, and we'll be happy to help.

Back to Top

Print Article