Are you tired of coming up with secure passwords for your students or employees? Look no further! I have a formula that will easily generate a list of strong passwords in Google Sheets.
Why Use Google Sheets for Password Generation?
Using a formula that incorporates usernames and dates of birth, you can create strong passwords that consist of a mix of alphanumeric and special characters. While I don’t recommend using these passwords for your important online accounts like banking or websites, they are more than suitable for internal use within your organization.
Steps to Generate Strong Passwords in Google Sheets
To get started, follow these simple steps:
-
In “Sheet1”, enter the usernames (student or employee names) in column A and their dates of birth in column B. Make sure to exclude spaces in the names.
-
In “Sheet2”, cell A1, enter the following formula to generate the alphabet letters from a to z:
=ArrayFormula({"Formula #1";lower(regexreplace(address(1,row(A1:A26)),"[^A-Z]",""))}) -
In column B of the same sheet, enter the special characters you wish to include in the passwords. Remember, you can use a maximum of 26 characters (26 rows).
- Now, in cell C1 of “Sheet1”, enter the formula to generate the password. Instead of providing an array formula that generates a whole list of passwords, I’ll provide you with a formula for just one password in cell C2. You can then drag it down to generate the desired number of passwords.
Let’s go through each step of the formula together:
Randomly Capitalize Letters in a Name in Google Sheets
To make the passwords stronger, we’ll mix lowercase and uppercase letters. We achieve this by splitting the characters in the name and capitalizing some of them.
Use the following formula to split each character in a name:
=split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),",")Then, to randomly capitalize some of the letters, we use the CODE function to return the Unicode map value of each character. The formula looks like this:
=ArrayFormula(if(iseven(code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))),code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))-32,code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))))Add Special Characters Randomly to the Password
Now that we have split the letters, we can join them back together to form a name and add special characters. Use the JOIN function to accomplish this:
=ArrayFormula(JOIN("",char(if(iseven(code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))),code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))-32,code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))))))Combine Formulas to Generate the Password
To complete the process, we combine the formulas mentioned above in the following order:
=FORMULA #7 & FORMULA #6 & FORMULA #8Additionally, we can extract certain digits from the date of birth, such as the last letter of the month, the last three digits of the year, and the day. This will further strengthen the passwords. Use the following formula to achieve this:
=FORMULA #7 & FORMULA #6 & FORMULA #8 & right(text(B2,"mmm"),1)&mid(text(B2,"yyy"),2,3)&day(B2)Finally, the complete formula to generate strong passwords in Google Sheets is:
=ArrayFormula(vlookup(mid(A2,2,1),Sheet2!$A$2:$B$27,2,0)&JOIN("",char(if(iseven(code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))),code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),","))-32,code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),",")))))&vlookup(right(A2,1),Sheet2!$A$2:$B$27,2,0)&right(text(B2,"mmm"),1)&mid(text(B2,"yyy"),2,3)&day(B2))Enter this formula in cell C2 of “Sheet1” and copy it down to generate multiple passwords.
That’s all there is to generating a list of strong passwords in Google Sheets! If you’re looking for a ready-made password generator template, feel free to use a copy of my Google Sheets template, available at Crawlan.com. Happy password generation!
