Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Sheets: Split Up Student Names

Break up student names written as Last, First into two columns.

Google Sheets: Split Up Student Names

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.

=split(A2, ", ")

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.

5 Easy Steps for Teaching with Digital Tools

Introducing digital tools into your teaching can seem challenging at first. There are many tools out there, and it might feel like there’s a lot to learn. But with easy steps for teaching with digital tools, you can start simply and gradually. There’s no need to dive in all at once – just take it one step at a time. Taking easy steps for teaching with digital tools starts with trying something!

Read More »
Students Prepare to Present

Help Your Students Prepare to Present

Maximize student success in presentations with ‘Speaker Notes by AliceKeeler,’ the ideal Google Slides add-on. Enhance how students prepare to present with easy transfer of speaker notes to Google Docs, promoting effective communication skills. Dive into the world of engaging, technology-aided presentations and empower students to shine in their academic endeavors. Discover the key to transforming student presentations into interactive, skill-building experiences.

Read More »

Infographic 7 Basic Steps for a Google Form

New to using Google Forms? This tool is essential for teachers to not only save time but to be adaptive to student needs. Use Forms to survey students, play games, personalize instruction, and assessment. This infographic on the 7 basic steps for a Google Form will help you get started.

Read More »

Online Workshop: Get Your Add-on Published

How can you make Google Workspace (Docs/Sheets/Slides) even better? You can create custom Add-ons with Google Apps Script. Wondering How to Get Your Google Workspace Add-on Published? Join Alice Keeler, Google Developer Expert, to learn the steps to get your Add-on officially published.

Read More »

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d