You may find yourself faced with a need to generate random character strings for various reasons.
Recently I needed to help a company set up email accounts for all of their employees on a new server. I was given the email address usernames but needed to create the passwords for each account. I didn’t want to be lazy and assign everyone the same password, or even an easily guessed sequential password, so I used the tip below to generate random character strings in Excel to give each user a unique and random password.
How to Generate Random Character String in Excel
In order to generate random character strings in Excel, you need to be able to generate random numbers, upper and lowercase letters, and special symbols. Excel has this functionality built in to help generate random character strings.
Generate Random Number String
To generate a random number string in Excel, simply use a formula like this:
=RANDBETWEEN(0,9)
This will randomly display a number between 0 and 9 each time the page is refreshed.
Generate Random Uppercase Letter String
=CHAR(RANDBETWEEN(65,90))
This will randomly display a letter between A-Z.
Generate Random Lowercase Letter String
=CHAR(RANDBETWEEN(97,122))
This will randomly display a letter between a-z.
Generate Random Symbol String
=CHAR(RANDBETWEEN(33,47))
This will randomly generate one of the following symbols: !”#$%&'()*+,-./
*Note: the CHAR() formula in Excel will generate a lot of other symbols as well. However, if the purpose is to create a password that someone may have to type on their keyboard, you want to keep it simple and avoid symbols like ¶, Œ, or ©. While these symbols can be typed with a special combination of keystrokes, it isn’t likely that someone will want to remember how to type each of these characters.
Generate a Random Password
Good passwords tend to have at least eight characters and are a mix of uppercase letters, lowercase letters, numbers, and symbols. To do this, simply combine the above formulas into one formula by simply adding an ampersand (&) in between each formula, like this:
=RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(33,47))&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(33,47))[/restrict]
This would create something like 6Ib&4Rj/ or 8Bs%3Xa+
You can have any combination of letters, numbers or symbols that you want by moving the formulas around. There’s also nothing saying you couldn’t have a longer password string by chaining on even more randomness to the formula.
A word of caution! If you are using this to generate a list of passwords, for example, be aware that the list will automatically change every time you refresh your worksheet, or change the value of a cell, etc. To ensure you don’t lose these values once they’re created, select the list of random passwords, copy and paste them as values. This will prevent them from updating again.
Another thing to consider is that if you need to make sure that all values are unique, this method isn’t foolproof. While it may not be likely that you will get the same string more than once, it certainly is possible. One easy way to check for duplicates is to use Conditional Formatting on the list of cells. First, select all the cells where you will generate random character strings. Then on the Home tab, Styles group select Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the first drop-down make sure “Duplicate” is selected, then choose a format in the drop-down. Click OK.
As your passwords are generated it will check for duplicates and highlight any that show up. I used this method on a quick test by copying the formula above down the entire column A (over 1 million random strings), and there were duplicates.