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

-

# 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`.

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.

-

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

-

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 :)

-

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.

-
+1 - I knew there should be a way - however, I would rather use INDEX instead of OFFSET - like `INDEX(B1:B10,RANDBETWEEN(1,10))` –  Jook Oct 18 '12 at 10:18