• Skip to primary navigation
  • Skip to main content

iSensei.tv

High Quality Apple and Tech Training

  • Productivity & Apple Tips
  • Apple in Education
  • Join 8700+ Online Students at Udemy
    • Effortlessly Switch from PC to Mac
    • Master the Basics of Using a Mac
    • Leverage Dropbox Cloud Storage
    • iPhone 101 (iOS 7 Version)
    • Manage WordPress from an iPad or Mac
  • About
    • Why iSensei.TV?
You are here: Home / Apple in Education / Useful Excel Functions for School Account Managers

Apple in Education / November 9, 2015

Useful Excel Functions for School Account Managers

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.

Screen Shot 2015-11-09 at 10.30.59 AM

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.

Screen Shot 2015-11-09 at 10.37.56 AM

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”))

Screen Shot 2015-11-09 at 10.45.05 AM

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).

Screen Shot 2015-11-09 at 10.49.56 AM

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.

Screen Shot 2015-11-09 at 11.07.48 AM

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.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)

Related

Filed Under: Apple in Education Tagged With: Account Manager, excel

Copyright © 2023 · Digital Pro on Genesis Framework · WordPress · Log in

  • Productivity & Apple Tips
  • Apple in Education
  • Join 8700+ Online Students at Udemy
  • About
 

Loading Comments...