How to Separate Names in Excel, Numbers, and Google Sheets

How to Separate Names in Excel, Numbers, and Google Sheets

Tutorials for Managing Your Class Rosters

Teachers have many reasons for working with rosters in different formats: creating spreadsheets, importing names into various websites and software, setting up a mail merge, etc. Typing names is time consuming and an easy way to make errors. Unfortunately most of us can only copy and paste from available rosters that are formatted as “LastName, FirstName.” This post will show you three options for splitting your roster into separate columns for first and last names.

Tutorials for Managing Your Class Rosters

How to Separate Names in Excel

1. Copy and paste your roster into Excel, then select the column containing all of your names.

2. Select the data tab.

3. Click “Text to Columns.”

How to Separate Names in Excel

4. Trust the Text Wizard and Click “Next.”

Text to Columns in Excel

5. Check the box beside “Column” (select “Space” if the first and last name are separated by a space instead of a comma).

6. Click “Finish.”


How to Separate Names in Google Sheets

1. Copy and paste your roster into Google Sheets, then select the column containing your names.

2. Click “Data.”

3. Select “Split text to columns…”

How to Separate Names in Google Sheets

By default, the names are separated by comma. You are given the option to change the separator – this is where you would select “space” if the first and last name were separated by a space without a comma.


How to Separate Names in Numbers

Unfortunately this is one feature of Numbers that is really inconvenient and I recommend using Google Sheets (or Excel) instead.

1. Copy and paste your roster into numbers (Note: Skip the first row or you won’t be able to easily copy your formulas.)

2. Paste the following formula into cell B2 (the first name you want to separate should be in cell A2):
LEFT($A2,(SEARCH(“, “,$A2)−1))
Note: remove the comma that appears between the quotes in the formula if the first and last name are separated by a space without a comma. 

3. Paste the following formula into cell C2:
MID($A2,(SEARCH(” “,$A2)+1),20)

4. Select cell B2. Click on the yellow dot at the bottom of the cell and drag it to the last row that contains names you want to separate. Repeat this process with cell C2.

How to Separate Names in Numbers
Digiprove sealCopyright secured by Digiprove


Don't Miss a Pick - Follow Us
Facebooktwittergoogle_pluspinterestyoutubemail
Nick LaFave
Profile photo of Nick LaFave

Add your Biographical Info and they will appear here.

Leave a Reply

Your email address will not be published. Required fields are marked *