How to create a random string of random length from the alphabet?

I want to create a random string with random length from the alphabet in Excel. For example, "jlskdjf", "kjlk", "kljsodif", etc. How can I do that?

Solution 1:

Basic

Assume you want to generate a random length (1-8 characters) string from lower case alphabets (a-z).

=LEFT( CHAR(RAND()*26+97) & CHAR(RAND()*26+97) & 
       CHAR(RAND()*26+97) & CHAR(RAND()*26+97) & 
       CHAR(RAND()*26+97) & CHAR(RAND()*26+97) & 
       CHAR(RAND()*26+97) & CHAR(RAND()*26+97),
       RAND()*8+1)

Each CHAR(...) generates 1 random lower case alphabet.

To use upper case alphabets (A-Z) instead of lower case, you can replace CHAR(RAND()*26+97) with CHAR(RAND()*26+65). Because ASCII code of A-Z is 65-90, and ASCII code of a-z is 97-122.

To simply the formula, you can use RANDBETWEEN() of Analysis Toolpak to replace RAND()*xx+yy.

Advanced

Assume you want to generate a random length (1-8 characters) string from specific characters.

You can input the desired characters in cell A1, for example:

abcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()

Then,

=LEFT( MID($A$1,RAND()*LEN($A$1)+1,1) & MID($A$1,RAND()*LEN($A$1)+1,1) & 
       MID($A$1,RAND()*LEN($A$1)+1,1) & MID($A$1,RAND()*LEN($A$1)+1,1) &
       MID($A$1,RAND()*LEN($A$1)+1,1) & MID($A$1,RAND()*LEN($A$1)+1,1) &
       MID($A$1,RAND()*LEN($A$1)+1,1) & MID($A$1,RAND()*LEN($A$1)+1,1),
       RAND()*8+1)

Each MID(...) gets 1 random character from A1.

Solution 2:

Hmm. It would be pretty easy with VBA to make a function to do it. With formulae it's a little more involved.

=CHAR(RANDBETWEEN(97,122)) obviously gives you one letter. So put ten of those in column A.
Then in the next column, put =A1 in cell B1.
Put =B1&A2 in B2, and fill down B2:B10. (CONCATENATE doesn't accept ranges, annoyingly.)
In cell C2, put =OFFSET(B1,RANDBETWEEN(0,9),0).

There might be an easier way, with array formulae or something.

Solution 3:

This formula doesn't require a cell with "abc...ABC...012"

Random 1 char from [a-zA-Z0-9].

=CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(48,57),RANDBETWEEN(65,90),RANDBETWEEN(97,122)))

If more chars is needed, copy CHAR(...) and separate them with &.

This solution is from Oaktree http://www.mrexcel.com/forum/excel-questions/332116-generate-random-alphanumeric-code.html