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 (18 characters) string from lower case alphabets (az).
=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 (AZ) instead of lower case, you can replace CHAR(RAND()*26+97)
with CHAR(RAND()*26+65)
. Because ASCII code of AZ is 6590, and ASCII code of az is 97122.
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 (18 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.
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+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
This formula doesn't require a cell with "abc...ABC...012"
Random 1 char from [azAZ09].
=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/excelquestions/332116generaterandomalphanumericcode.html
As a formula for a single character you could use this
=CHAR(RANDBETWEEN(97,122))
Just look in any ACSIITable 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 VBAModule, 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 120 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 :)
=LEFT(CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&... repeat as many times as needed, RANDBETWEEN(minimum length, maximum length))
(If you want lettersonly 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:
 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.  generate a random number between 8 and 12, the string length
RAND()*4+8
 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 09 and the letters AZ 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.

OP was specifically asking for alphabet characters only. Can you edit your answer to address this? – music2myear Jan 18 '17 at 1:07

@music2myear done, but in this way you lose the conciseness of the original version. – CristianCantoro Jan 19 '17 at 18:24

Conciseness is only useful if it meets the needs. This answer, even though it is longer, is better because it actually answers the specific question OP asked. – music2myear Jan 19 '17 at 18:59
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