How often do you get a list of student names in the format of Last, First? You want to separate the first and last name. Here are a few ways to do this.
=split
One way of splitting up text in a cell to multiple cells is =split(). This will split up the cell reference at the delineator. What that means is you indicate what cell has the names you want to break up. Put a comma. Use quotations for what you using to indicate where to break it up.
Last Comma First
In the case of Last, First you want the last name in one cell and the first name in another cell. What is preventing you from doing that is a comma AND A SPACE!!! It’s so easy to miss realizing the space between the names. If the name is in A2 then in B2 (or any blank cell) I start with an equals sign and put =SPLIT( and then click on A2 so it automatically is added to my formula. Put a comma. IN QUOTATIONS put the delineator. In this case it is “, ”
LOOK CAREFULLY that is quotation comma space quotation.
Use the Fill Down square to copy the formula for the entire list of names.
Paste Special
The problem with this method is that it creates a formula and is messy when you try to copy and paste the names to other parts of the spreadsheet. It is no problem if you are copying and pasting the names to a Google Doc.
Within the spreadsheet, when you copy and paste the name you end up copying and pasting the formula.
The solution is to highlight the resulting first and last names and using Control C to copy. Immediately use Control SHIFT V to paste in the same spot.
Control SHIFT V is paste special.
Spreadsheet of Student Names
If you are a Google Classroom user I have coded alicekeeler.com/reusegc to, amongst other things, allow you to easily get a roster of your students in a spreadsheet. After making a copy of the Add-on, use the Add-on menu to show the sidebar. Use the button to get your classes, check the checkbox to set the class. You only need to do this once. Under “Assignments” use the “Show Roster” button to pull your roster into the spreadsheet. Choose “Pop out roster” to export it to a new spreadsheet.
Text to Columns
Another method is to highlight the names and use the Data menu to choose “Split text to columns.”
Sometimes the spreadsheet predicts how you want to split up the names and you are good to go. This is NOT a formula so the values are ready to use without paste special.
Separator
Notice there is a small pop up that says “Separator” when you choose “Split text to columns.” The default is “automatically” but this doesn’t always work. You may need to click on the drop down to select “Space” or “Comma.”
Caution: Have Room
Regardless of which method you use, you will need blank columns available for the names to go into. If you have the names in column A and something else in column B you will need to insert blank columns before splitting to ensure you have a spot for the name to go into.
Tip: Add extra columns
Sometimes there is a space that you don’t notice at the end of the name, or a hyphen or, two middle names, etc… When I split text to columns I will insert 5 blank columns and after splitting deleting the blank columns that did not get used. This ensures that I do not accidentally override other information on the spreadsheet.
You must log in to post a comment.