Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 6 down vote accepted

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
    
+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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.