HomeGoogle DocsSpreadsheetHow to Generate a List of Passwords in Google Sheets

How to Generate a List of Passwords in Google Sheets

Published on

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.

Data to generate a list of passwords in Google Sheets

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.

Steps to generate a list of passwords in Sheets

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,"),",")))
Code function to randomize upper and lower cases

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,"),",")))))
Generating strong passwords in Sheets with upper/lower mixture

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 # 8

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

Formula to generate a list of passwords in Google Sheets

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.

Sample050819

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here