I was given the task to give passwords and usernames to 185 kids. I said, “Easy. Do you have an official list of students?” Luckily, they had a list in Excel. So I immediately went to work.
I copied the list of students to another worksheet (I try NEVER to work on originals, so if I make a mistake, I can always open the original list). Last Names in one column then First Names. So the log-in name of the student will be the first letter of their given name then their entire family name. If their name is John Smith, the log-in name will be jsmith.
But to do it for 185 kids? One by one? Getouttahere! Excel can do it for you!
- Arrange the given names as the first column, then the family or surnames as the second column. Type in the function CONCATENATE (meaning, combine text from two cell references).
- It will look like this: =LOWER(CONCATENATE(LEFT(A1,1),B1))
- The LOWER function makes the entire text lower case.
- If I simply typed in =CONCATENATE(A1,B1) the result will be JohnSmith.
- That extra little command, LEFT(A1,1) tells Excel to check the text in A1, get only ONE character from the left (that’s what the “1” means).
- Reducing John Smith into jsmith!
What if you have brothers and sisters who have the same first letters in their names, like John and Jane Smith? They’ll be both jsmith!
- Select the Home Tab, then Conditional Formatting/Highlight Cell Rules/Duplicate Values.
- It will format any duplicates in red letters, making it easy to see which log-in names are duplicated and in need of changing.
For passwords, I was instructed to make it an 8 character alphanumeric password with an uppercase letter. Let’s say I want
- An 8 character password with numbers as the last two characters.
- Randomly capitalized letter in the word.
Online, I found several lists of common six-letter words (six because the last two are supposed to be numbers.
I copied and pasted the entire list to Excel (Select the list, Ctrl C, go to Excel, Ctrl V). Unfortunately, the list was numbered from 1 to 1,631. I had to separate the numbers from the words.
- Select the column with the mixed up words and numbers.
- Click on the Data Tab, click on Text to Columns, select Delimited then click on Next.
- Make sure there is a check mark on “Space” among the delimiters. This will separate the numbers from the text. Click ok. Delete the column with numbers.
- Select 185 of easy to remember words. This one I had to do manually!
Now, I want to combine the six-letter words and the two-digit numbers AND I want to randomly capitalize a letter in the word (not necessarily the first letter). How do you do that?
- In the column besides the word, type =RANDBETWEEN(1,6). This will generate a random number from 1 to 6 (that’s the number of letters the word has).
- In the column besides that random number, type this jawbreaking equation:
- The random number (from 1 to 6) will determine which letter will be capitalized.
- CONCATENATE will combine
-the letters to the left of the capitalized letter: that’s the LEFT (A1,B1-1) in the formula above.
-the capitalized letter itself: UPPER(MID(A1,B1,1)
-and the letters to the right of the capitalized letter: MID(A1,B1+1,6-B1)
- Try it! The letters will randomly capitalized like some kind of cool animation! Only works with 6-letter words, though.
The two digit numbers
- Generate a column of 185 random, two digit numbers. Since there are 185 of them, obviously some numbers will be repeated. Use =RANDBETWEEN(10,99) to create a column of 185 random two-digit numbers, copy the numbers (Ctrl C) and paste them (use paste special, Ctrl Alt V, select “Values”.) besides the six letter words with the random capitals.
- Again, use concatenate to combine the randomly capitalized six-letter words and the two-digit numbers and you have a set of passwords that are hard to guess yet fairly easy to remember!
- Copy the list and paste it besides their log-in names. I suggest you use Paste Special/Values so that the capitalizations won’t change every time you open the Excel file.