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($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.

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

Answer 5

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

Answer 6

(If you want letters-only strings see the added paragraph below)

Generate a random string of uppercase letters and digits of random length between 8 and 12: =MID(BASE(RAND()*10^18,36,12),1,RAND()*4+8)

Explanation:

1. generate a random string of digits and uppercase letters, of minimun length 12: BASE(RAND()*10^18,36,12). The trick is generating a big random number and then converting it to base 36 obtaining something that effectively looks like a string.
2. generate a random number between 8 and 12, the string length RAND()*4+8
3. take the characters of the string as in (1) from 1 to the lenght you generated in (2).

Functions used in the formula (inside out):

• RAND() Returns a random number between 0 and 1.
• BASE(Number; Radix; [MinimumLength]) Converts a positive integer to a specified base into a text from the numbering system. The digits 0-9 and the letters A-Z are used.
• MID("Text"; Start; Number) Returns a text string of a text. The parameters specify the starting position and the number of characters.

The number 10^18 is a magic number for which the generated string do not have trailing or leading zeroes. If you need to create longer string I would suggest to create two or more such strings and concatenate them.

Plase note: this solution was tested on LibreOffice Calc 5, but it should work also in Microsoft Excel since the functions are the same as per their documentation (which I can not link because I do not have enough reputation).

Edited answer

Since it was pointed out in a comment that the OP specifically asked for letters only I will throw this alternative version in: =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BASE(RAND()*10^22,36,16),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),1,RAND()*4+8)

where you substitute all the occurrences of digits to empty strings. In this way you obtain only (uppercase) letters. I have tweaked the magic numbers to count for the possibility that the initial random string has many digits. It is not failsafe, though in the sense that in principle you could obtain strings that are shorter than expected.

If you need to be sure of the minimum length then you have this other (even more complicated) alternative: =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BASE(RAND()*10^22,36,16),"0",CHAR(RANDBETWEEN(65,90))),"1",CHAR(RANDBETWEEN(65,90))),"2",CHAR(RANDBETWEEN(65,90))),"3",CHAR(RANDBETWEEN(65,90))),"4",CHAR(RANDBETWEEN(65,90))),"5",CHAR(RANDBETWEEN(65,90))),"6",CHAR(RANDBETWEEN(65,90))),"7",CHAR(RANDBETWEEN(65,90))),"8",CHAR(RANDBETWEEN(65,90))),"9",CHAR(RANDBETWEEN(65,90))),1,RAND()*4+8)

where you substitute each digit with a randomly generated uppercase letter.

I have to say that what I liked of the initial solution with respect to the ones provided in the other answers is that it is concise and relatively straightforward to understand. The two alternatives lose these properties.

Answer 7

To generate random chars:

In Cell B1

=CHAR(RANDBETWEEN(48,131)) (or whatever char set you want)

Fill this across to the number of columns = maximum required chars in your string

To generate random length

In Cell A1

=LEFT(CONCATENATE(B1,C1,D1,E1,F1,G1,H1,I1,J1,K1),RANDBETWEEN(0,9))

To generate random strings

Fill the whole table down in column A1