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

Question

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?

Answer 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(A1)+1,1) & MID($A$1,RAND()*LEN(A1)+1,1) & 
       MID($A$1,RAND()*LEN(A1)+1,1) & MID($A$1,RAND()*LEN(A1)+1,1) &
       MID($A$1,RAND()*LEN(A1)+1,1) & MID($A$1,RAND()*LEN(A1)+1,1) &
       MID($A$1,RAND()*LEN(A1)+1,1) & MID($A$1,RAND()*LEN(A1)+1,1),
       RAND()*8+1)

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

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

Answer 3

As a formula for a single character you could use this

=CHAR(RANDBETWEEN(97,122))

Just look in any ACSII-Table to select your desired rand range.

But the random length is tricky, not because of the random length, but because of the random characters you want to put together. Otherwise, you could just the REPT function joined with RAND function and the formula above.

But to fit your described result, I would use this code:

'Put this into a VBA-Module, to be accessable as a worksheet function
Public Function RandomString() As String
  Dim i As Long
  Dim lngEnd As Long
  Dim strResult As String

  With Application.WorksheetFunction

    lngEnd = .RandBetween(1, 20) 'String length 1-20 characters
    strResult = ""

    'create a random string of a random length between 1 and 20
    For i = 1 To lngEnd
      strResult = strResult & Chr(.RandBetween(97, 122))
    Next i

  End With
  Debug.Print strResult
  RandomString = strResult 'return the random string
End Function

If there is a solution to do this code with formulas, hence without VBA, I would very much like to know about it :)

Answer 4

=LEFT(CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&... repeat as many times as needed, RANDBETWEEN(minimum length, maximum length))