You may find yourself faced with a need to generate a ton of random strings of characters.
For example, you may have a list of usernames that need passwords – you could be lazy and say everyone’s password is password, but I will assume you know better than that!
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 ©.
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))
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.
=CHAR(69)&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(65;90))&RANDBETWEEN(10000;99999)&CHAR(RANDBETWEEN(99;103))&CHAR(RANDBETWEEN(87;90))
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.