Last name, first name as separate cells

[lkiesling]

This idea has been developed and deployed to Canvas

---

I've just used my exported gradebook to determine the grade distribution for a 150-student class. To determine the distribution I sorted the data in order from highest to lowest final score, and then went back to alphabetize the data so I could submit the grades ... only to realize that the student names are (first name last name) in the same cell. This means I have to alphabetize *manually*.

This is terrible database design. Why isn't the last name in as a separate cell to enable automatic alphabetizing? Is there a workaround in the short run? Not that it will help me today as I enter my grades, but ... And the ability in Canvas to search on a student name is a poor substitute for sensible database design.

[monica_a]

Great, but how you do you get that comma in there?

[monica_a]

Yes, this would be very helpful for me, as well. I have exactly the same issue.

When I export grades from the gradebook into a CVS or excel file, it would be helpful to have separate columns for the first and last names. This would allow me to sort and resort the final grades.

Background: Right now, after I export grades, the grades are sorted by Last name automatically. But as soon as I start analyzing the data, I mess that up. I sort grades by score, and assign the final grade. Then I need to sort that back into alphabetical order by last name in order to submit the grades to my school. I don't upload the grades back to Canvas, but instead use my own files to manually input the grades. But currently when I sort them back, the grades end up sorted by alphabetical order of first names.  If last names are exported to a different column, then we teachers would be able sort the grades however we wanted, including by last name after several other sorts.

This is what we have now:

First and last names in the same column, sorted by score:

   

First and last names in the same column, sorted by first name (not helpful)

This is what I am requesting:

First and last names in different columns, sorted by last name

This is less elegant, but would also serve my purposes:

First and last names in the same column, Last name listed first

[jbuchner]

Doesn't have to be a comma. I use a Mac, and Excel 2011, but when I highlight a name column with first and last name separated by a space, and do data->text to columns-> a wizard opens up, with two radio buttons. If the column does not contain commas, the "Delimited" button is hit by default. After hitting next, the delimiter options include space, tab, semicolon, comma or "other". The third step lets you set the data formats of the new columns. 

Let me know if you get stuck.

Here's one thing I checked. If "Ernie von Schledorn" is in your class, this will create three columns for the whole class. So, if you have a name like that, you need to manually remove a space, vonSchledron, or fix it after you split it.

[monica_a]

Ah, I used a space as the delineator. It worked! Thanks, John!

   

Just remember to add an extra column before starting this process, otherwise whatever in the column on the right will be written over.

[kaw]

Not a good work around when people have 3, 4 or even 5 names.  Getting scantron raw files to match names creates lots of errors. 

[monica_a]

Yes, I agree that we need a solution that sorts names consistently across different systems, including when people have 3, 4 or 5 names. Even "last name" and "first name" is misleading, because what we really need is to be able to sort surnames and given names.

[pbhanney]

I am finding the same problem, but with a different reason. When my students create their accounts and place their name in the "Full Name" box Canvas automatically will look for the last space in the name and will make anything after the last space the last name. So, for instance, if I have a student with the name Pablo de la Cruz, Canvas will automatically put "Cruz" as the last name and "Pablo de la" as the first and middle names. Many of our students come from Central or South America so they will have two surnames (i.e. Maria Sanchez Perez). In this example "Sanchez Perez" would be Maria's surnames, but Canvas will only select "Perez" as her surname and list "Maria Sanchez" as her given names. This can cause a problem as I would be looking in the "S" range of students for Maria when she is listed in the "P" range on Canvas.

[jbuchner]

A work-around solution for those with people with as many names as they want:

Get your class list, open in excel.

Insert as many columns as you need for names plus one more for "Ernie von Schledorn" or "Maria Sanchez Perez", you want to insert THREE columns, for "Pablo de la Cruz" insert four.

Under Data: Go to Text to Columns, use the wizard, use delimitated columns, and click on the boxes for the different delineators you want to use. (In the three example names, you only need to have spaces, but there are other options, and even an "other" box)

When you hit finish, the names with be broken out into their new columns. (Ernie/von/Shledron, Maria/Snachez/Perez, Pablo/de/la/Cruz) but there should still be an empty column to the right.

In that empty column you want to type a formula that will combine all but the First Name Column. The formula in my example is =B1&" "&C1&" "&D1. (The contents of B and a space and the contents of C and a space and the contents of D.) There is a space in quotes. You can then flash fill the empty column with the formula.

Does that help?

[jbuchner]

I proposed a solution for this below.

[mdurler]

I posted a similar fix several months ago that uses a single extra column and formulas.