If you’ve ever had to create User Accounts from a database, you know the usefulness of common Excel functions. Perhaps I’ll point out a function or two that you don’t use, but would be helpful to you. If you’re new to Student Account Management, these functions will make your life much easier.
And for an advanced tip, we will daisy chain a number of functions together to create the perfect Student ID.
A Typical Account at a School or Small Business
Most schools or small businesses create an email address for their employees that are based on their First and Last name (aka their Given and Family Name). For this example, we will use my name “Arlen Nagata” and this iSensei.tv domain.
Create a simple username with CONCATENATE Excel Function.
=CONCATENATE() is the Excel Function to combine multiple cells or strings.
In our example I am combining three things: Cell A2, Cell B2, and the Domain Name “@isensei.tv”. Note that to add a string of text like @isensei.tv, I’ve wrapped it in quote marks. This is important and it won’t work without it.
Create a First Initial Last Name Username, Add LEFT Excel Function
=LEFT() is an Excel Function to pull a certain number of characters starting at the Left.
=LEFT(A2,1) Will take the Data in Cell A2 and give you just the first character. =LEFT(A2,2) would give us the first two characters and so on.
Let’s add the LEFT() function inside of our CONCATENATE() function to get just my first initial.
Create a lowercase username, Add the LOWER Excel function
In this example, I want all the letters to be lower case so I will wrap the entire function in the LOWER() function. Alternatively, you could wrap each cell to get the same result. Here are the two examples.
=LOWER(CONCATENATE(LEFT(A4,1),B4,”@isensei.tv”))
=CONCATENATE(LOWER(LEFT(A4,1)),LOWER(B4),”@isensei.tv”))
Our School Adds the Graduation Year to the End of Student Emails
To do that, we just add the C5 cell after the B5 Cell (each separated by commas).
Sanitizing Usernames using the SUBSTITUTE Excel function
Sanitizing isn’t the right term here, but what we want to do is avoid problem usernames. Problem usernames would occur when there are characters in a person’s name don’t translate to email well.
A few problem areas are: Hyphenated Names, Apostrophes, Spaces Between Names and sometimes periods.
To Remove those from the names before CONCATENATE them together, we use a string of SUBSTITUTE() Functions. *If you can simplify this function, I would love your help!
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,” “,””),”-“,””),”‘”,””),”.”,””)
This Function will remove Spaces, hyphens, Apostrophes, and Periods.
I’ll give you a few examples of problems and how this Function clears up these problems.
Click on the image to get a larger image.
Here’s the Function I use:
=LOWER(CONCATENATE(LEFT(A11,1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B11," ",""),"-",""),"'",""),".",""),C11,"@isensei.tv"))
You can see that this is a complex function, but once you’ve created the function that works for all your Employees and Students, you never have to create it again and it will create your usernames and email addresses effortlessly.
Questions About These Excel Functions?
Do you have any questions about the complex function, why I placed the functions where I did, etc? Leave me a comment or drop me an email and I’ll get back to you.