You can use my formula to easily generate a list of passwords in Google Sheets. To create passwords I am using user names and their date of births. The passwords will be strong as it will be a mix of alphanumerics and special characters.
You can use my formula to generate strong passwords in Sheets for your students or for employees in your organization to access their office-related accounts.
I don’t recommend you to generate passwords using my formula for password protecting your important online accounts like banking, blog/web sites, domain, hosting, insurance, etc. But that doesn’t mean the Google Sheets formula generated passwords are weak.
Steps to Generate a List of Strong Passwords in Google Sheets
As I have mentioned above we are going to generate the passwords in Google Sheets using user names and their DOB.
In “Sheet1”, enter user names, I mean student names or employee names, in column A and their DOB in column B. Do not include space in the names. For example, “Prashanth KV” must be entered as “PrashanthKV”.
In “Sheet2”, cell A1 enter the following formula that generates the alphabets a to z.
Formula # 1:
=ArrayFormula({"Formula # 1";lower(regexreplace(address(1,row(A1:A26)),"[^A-Z]",""))})You can find the formula explanation here – How to Autofill Alphabets in Google Sheets.
In another column, that is column B, in the same Sheet, enter the required special characters that you wish to include in your password. A maximum of 26 characters (26 rows) is allowed.
Now what we want is the formula to generate the password in cell C1 in “Sheet1”.
I am not going to provide you an array formula that generates a list of passwords in Google Sheets. Instead, you will get a formula in cell C2 in “Sheet1” that you need to drag-down. This is to make sure that the provided password is strong enough to use.
Let me take you thru’ each and every step in writing the formula. So that, if you want, you can later tweak the formula. Here are the steps.
Randomly Capitalize Letters in a Name in Google Sheets
To make a password strong, it is advised to mix the passwords with lowercase and uppercase letters.
As I have said, we are going to generate a list of strong passwords in Docs Sheets. So we can’t skip this step. See how I am capitalizing few of the letters in a name.
To capitalize letters in the middle or after the first letter in a name, we must split the characters in the name.
Sheets Formula to Split Each Character in a Name
To split each character in a name, you can use either of the below formulas. I am using the second one though.
=ArrayFormula(mid(A2,row(A1:indirect("A"&len(A2))),1))For this formula explanation, please check this guide – Split number to digits in Google Sheets.
I am using the following REGEX formula to split the letters in a name to individual columns.
Formula # 2:
=split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),",")The REGEXREPLACE in the above formula places a comma between each letter in the name. The SPLIT is used to split the letters into new columns based on the comma delimiter. The LOWER makes all the letters lowercase.
To randomly capitalize some of the letters, we can depend on the CODE function as a base.
Code Function to Randomly Capitalize Letters in a String
We can use the CODE function here to return the Unicode map value of each character in the name. For that just enter the above formula within CODE and hit Ctrl+Shit+Enter or wrap the whole formula with ArrayFormula.
Formula # 3:
=ArrayFormula(code(split(REGEXREPLACE(LOWER(A2),"(.{1})", "$1,"),",")))
Then what we are going to do is test whether the returned Unicode values are odd or even. If it is even, converts it to upper. For that use ISEVEN with IF as below.
Generic Formula:
=ArrayFormula(if(iseven(Formula # 3),Formula # 3-32,Formula # 3))I know you can understand this generic formula except the -32, right?
The Unicode values of the small case letters (a-z) are from 97 to 122. Then what about the upper case letters (A-Z)? It’s from 65 to 90.
That means you can convert a lower case letter to upper case by subtracting 32 from the Unicode value. Here is the formula equivalent to the generic formula above.
Formula # 4:
=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,"),","))))The above formula returns some modified Unicode values. Wrap that with the CHAR function to convert the Unicode values to characters.
Formula # 5:
=ArrayFormula(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,"),",")))))
Add Special Characters Randomly to the Password
We are one step closer to generating a list of passwords in Google Sheets. Here in this step we will combine the split letters to form a name and will add special characters to it.
To join the split letters back to the name, use the JOIN function. I mean do wrap the above long formula with JOIN.
Formula # 6:
=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,"),","))))))The formula converts “William” in cell A2 to wiLLiam. Cool, right?
Let us leave the above formula in cell C1. With a VLOOKUP we can get special characters from the list in “Sheet2”. How?
For that, I will use the second character in the name as the search key and use the data in Sheet!2A2:B27 as the range. To extract the second character, we can use the MID function.
Formula # 7:
=vlookup(mid(A2,2,1),Sheet2!$A$2:$B$27,2,0)I am extracting one more special character. This time I am using the last letter in the name as the search key. Instead of MID, we can use the RIGHT function this time. Key the following formula in cell E2.
Formula # 8:
=vlookup(right(A2,1),Sheet2!$A$2:$B$27,2,0)Formula to Generate a List of Passwords in Google Sheets
Time to combine formula 6, 7, and 8 in the below order.
=FORMULA # 7 & FORMULA # 6 & FORMULA # 8With the above combo, we can add the date of birth which is in column B with the password. But instead of adding the date, we will extract certain digits from the date.
I mean the last letter of the month, the last three digits of the year and the day. Thak can make the PW even stronger.
=FORMULA # 7 & FORMULA # 6 & FORMULA # 8 & right(text(B2,"mmm"),1)&mid(text(B2,"yyy"),2,3)&day(B2)Final Formula to Generate Strong Passwords in Google Sheets:
=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 and copy down.
That’s all about generating a list of strong passwords in Google Sheets. If you are looking for a password generator template in Sheets, feel free to use the copy of my Sheets below.
