Recently I needed to take a class roster and create usernames to upload into Moodle. The problem was that it wasn’t in a pretty format. This means that I needed to get clever with Excel functions.

What I Had

  1. The full name in the format “lastname, firstname”
  2. Email

What I Wanted

I wanted to end up with 4 rows:

  1. Last Name
  2. First Name
  3. Username (first initial and surname)
  4. email

Walkthrough

Step 1 – Make a New Row Next to the Names

Screen Shot 2014-04-07 at 12.11.22 PM

Step 2 – Get Rid of the Space After the Comma Using Find & Replace

You have to eliminate the space at some point so you might as well do it now.

Screen Shot 2014-04-07 at 12.11.48 PM

Just type a single space into the find field and leave the replace field blank.

Screen Shot 2014-04-07 at 12.12.46 PM

Step 3 – Use “Text to Columns” to Split the String

Text to columns allows you to move portions of one cell into the cell next to it.

Screen Shot 2014-04-07 at 12.12.56 PM

Choose “Delimited”. This allows us to select a delimiter, or ‘thing that separates the things we care about’.

Screen Shot 2014-04-07 at 12.13.02 PM

Select “Comma” as your delimiter. i.e. the function will take everything to the right of a comma and put it into the cell to the right.

Screen Shot 2014-04-07 at 12.13.09 PM

Neat, huh?

Screen Shot 2014-04-07 at 12.13.19 PM

Step 4 – Create Username Column and Insert Function

Screen Shot 2014-04-07 at 12.16.09 PM

You can see the function at work in the picture.  Copy it here:

=LOWER(CONCATENATE(LEFT(B2,1), A2))

Explanation of this:

  • CONCATENATE means “Combine two things together into one”. We want to put the first letter of their first name onto the front of the full last name. So we need to tell it where to find those two things
  • LEFT(B2,1),A2) = Starting from the left of the cell, get x number of characters. This specifies cell B2 and gets 1 character (i.e. the first letter).
  • LOWER just turns everything lowercase

Note: Notice that my columns are setup with the last name first, but I want the username to be first initial followed by last name. That’s why the LOWER function looks how it does. It first gets the first initial and then gets the last name. If your columns are the other way, then you’d have to flip it.

How Did I Know How To Write That Function?

I didn’t. As a rule, knowing how to do something isn’t as crucial as knowing what you want to do. I didn’t know how to do what I wanted, but I knew how to describe it and that’s really all you need. Nine times out of ten, just saying what you want to do into Google will return exactly what you wanted.

excel_make_username_from_last_name_and_first_initial_-_Google_Search

Straightforward question yields quick results from StackOverflow.

Excel__How_do_I_take_the_first_character_of_1_cell_and_prepend_it_to_another_cell_and_place_it_in_a_3rd_cell__-_Stack_Overflow

Someone else has asked the same question you are asking before. There’s no exception to this rule.

Step 5 – Fill Down

Excel has a function to copy the function. Just select the cell with the function in it and click the little circle on the bottom right corner of the box. Pull down and you’re done!

Or you could select all the cells in the column, select “Edit>Fill>Down” or follow the pictures here:
Screen Shot 2014-04-07 at 12.18.59 PMYou’re Done

Hooray! You just saved a lot of time and sweat!

Screen Shot 2014-04-07 at 12.19.06 PM