cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Community Member

How do I sort student quiz data by LAST name?

Jump to solution

When I pull quiz data (Quizzes > Quiz Summary > Student Analysis), the file exports students' name in 1 cell (first name first, last name last). Is there a way to export this data and sort by last name (either last name first or separate columns for first and last names)? 

1 Solution

Accepted Solutions
Highlighted
Navigator

dduggan@midwestern.edu

There isn't any option you can choose within Canvas to change the format of the export. That means that any splitting will need to be done with an external program.

Excel isn't a great program for text handling, but here are some options.

One option is to use the built-in text-splitting capability. This really only works if all of your student names are consistently entered, most likely with just a single space in their name.

  1. Insert a blank column after the name column. Give the column an appropriate header like "last" or "surname."
  2. Highlight the values in the name column
  3. Go to Data > Text to Columns
    1. You have Delimited data. Then click Next.
    2. Your delimiter is a just a space and nothing else. You will probably want to treat consecutive delimiters as one as well. Now click Finish (step 3 is optional and not needed for names).
  4. You will now have the first name in column 1 and the last name in column 2.

If you have more than one space in the names, you need to add extra columns to allow for the extra words. It's kind of a hassle. For example, if you have "Old St. Nick", Old will be in column A, St. in column B, and Nick in column C.

You probably want St Nick to go together, so you could add another column (say D) and use (provided you have a recent version of Excel) the TEXTJOIN() function. In cell D2, you would type the following formula, hit enter, and then copy it down.

=TEXTJOIN(" ",TRUE,A2:C2)

That joins all the words with spaces, ignoring any empty cells so you don't get two spaces.

If you want to recombine it into a single name, just with the last name first, then you could use

=TEXTJOIN(" ",TRUE,B2:C2,A2)

Another option when there is just a single space or you're okay living with just a few that have more than two words in their name is to use a formula.

  1. Insert a blank column after the name column. Give it a name like "last" or "surname."
  2. Enter this formula in cell B2.
    =RIGHT(A2,LEN(A2)-FIND(" ",A2))
  3. Copy that formula down the B column for all of your names.

If you have more than one space in the names, for example "John Q Public", then it would sort pull out "Q Public" instead of "Public."

The alternative is to use a reverse find that will locate the last space in the string and take everything after that as the last name. You do not need an extra column for the first name, just use the original.

The problem is that there is no built-in reverse find in Excel. The formula to create it is long. It's probably better written in visual basic for applications. You can Google search for reverse find in Excel if you need to do this.

Depending on what you have available to you and how techie you are, a program to split it might be the easiest. Computer programs are much better at splitting up text than Excel is.

View solution in original post

2 Replies
Highlighted
Navigator

dduggan@midwestern.edu

There isn't any option you can choose within Canvas to change the format of the export. That means that any splitting will need to be done with an external program.

Excel isn't a great program for text handling, but here are some options.

One option is to use the built-in text-splitting capability. This really only works if all of your student names are consistently entered, most likely with just a single space in their name.

  1. Insert a blank column after the name column. Give the column an appropriate header like "last" or "surname."
  2. Highlight the values in the name column
  3. Go to Data > Text to Columns
    1. You have Delimited data. Then click Next.
    2. Your delimiter is a just a space and nothing else. You will probably want to treat consecutive delimiters as one as well. Now click Finish (step 3 is optional and not needed for names).
  4. You will now have the first name in column 1 and the last name in column 2.

If you have more than one space in the names, you need to add extra columns to allow for the extra words. It's kind of a hassle. For example, if you have "Old St. Nick", Old will be in column A, St. in column B, and Nick in column C.

You probably want St Nick to go together, so you could add another column (say D) and use (provided you have a recent version of Excel) the TEXTJOIN() function. In cell D2, you would type the following formula, hit enter, and then copy it down.

=TEXTJOIN(" ",TRUE,A2:C2)

That joins all the words with spaces, ignoring any empty cells so you don't get two spaces.

If you want to recombine it into a single name, just with the last name first, then you could use

=TEXTJOIN(" ",TRUE,B2:C2,A2)

Another option when there is just a single space or you're okay living with just a few that have more than two words in their name is to use a formula.

  1. Insert a blank column after the name column. Give it a name like "last" or "surname."
  2. Enter this formula in cell B2.
    =RIGHT(A2,LEN(A2)-FIND(" ",A2))
  3. Copy that formula down the B column for all of your names.

If you have more than one space in the names, for example "John Q Public", then it would sort pull out "Q Public" instead of "Public."

The alternative is to use a reverse find that will locate the last space in the string and take everything after that as the last name. You do not need an extra column for the first name, just use the original.

The problem is that there is no built-in reverse find in Excel. The formula to create it is long. It's probably better written in visual basic for applications. You can Google search for reverse find in Excel if you need to do this.

Depending on what you have available to you and how techie you are, a program to split it might be the easiest. Computer programs are much better at splitting up text than Excel is.

View solution in original post

Highlighted

"There isn't any option you can choose within Canvas to change the format of the export."

Since we can sort the grade book by last name, why didn't the Canvas programmers think to make this option available everywhere else, while they were working on that problem?

Now, I have to make a suggestion, wait (at least) 6 months until it gets voted on, and then wait (at least) 6 months until it gets programmed, and then wait (at least) 6 months until my institution gets the new version.

Put those two issues together, and you can see why I don't like working with Canvas.

((A few minutes later))

Make that three. Someone asked about this very feature five years ago!

0 Kudos
Top Kudoed Authors
Labels