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.
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.”
4. Trust the Text Wizard and Click “Next.”
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…”
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.
Don't Miss a Pick - Follow Us
