Generating Random Strings

Richard has a need to generate random 15- and 20-character strings, and he thought he might be able to do it in Excel. The strings can contain letters and digits, with no spaces. He knows about the RANDBETWEEN function, but doesn't really see how it could be helpful for his purposes.

Actually, you can use the RANDBETWEEN function as the core of your formula. Start by putting into cell A1 all the possible characters that you may want included in your strings. For instance, you could put the following 62 characters in A1:

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789

Now, consider the following formula:

=MID(A1,RANDBETWEEN(1,LEN(A1)),1)

This uses the MID and RANDBETWEEN functions to select a single random character from whatever characters you placed in cell A1. Now, to lengthen what is returned, you just need to start combining this core formula using the ampersand character. For instance, the following will return 2 characters:

=MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)

If you want to return 15 characters, then you just need to use 15 iterations of the core formula:

=MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)&MID(A1,RANDBETWEEN(1,LEN(A1)),1)

This can (obviously) lead to some very long formulas. Fortunately, if you are using the version of Excel provided with Microsoft 365, you don't need to rely on super-long formulas to generate the strings you want. You can, instead, use the powerful RANDARRAY function combined with the CONCAT function.

You already have your base characters in cell A1. So, in cell A2 place either 15 or 20, depending on the length of string you want to generate. (Actually, you could put any other length in A2, but Richard specifically needed 15- and 20-character strings.) Now, use the following formula:

=CONCAT(MID(A1,RANDARRAY(1,A2,1,LEN(A1),TRUE),1))

The result is a random text string, just the length you want.

If you would rather use a macro, then you can easily create a user-defined function that will return a random-character string of any length you desire:

Function RandChars(iLen As Integer) As String
    Dim sSource As String
    Dim J As Integer
    Dim sTemp As String
    Dim AWF As WorksheetFunction

    Set AWF = Application.WorksheetFunction

    sSource = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    sSource = sSource & "abcdefghijklmnopqrstuvwxyz"
    sSource = sSource & "0123456789"

    sTemp = ""
    For J = 1 To iLen
        sTemp = sTemp & Mid(sSource,AWF.RandBetween(1, Len(sSource)), 1)
    Next J
    RandChars = sTemp
End Function

The characters you place into sSource are those that will be used to put together the random string. The macro utilizes the RANDBETWEEN worksheet function to pull the characters from sSource.

To use the macro, simply use a formula like this in your worksheet:

=RandChars(15)

The parameter you pass to the function determines the length of the string that is returned by the function.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12823) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.