January 26, 2022

Generate Random Strings in Excel Automatically

How to generate random strings in Excel automatically using a macro. Watch the video below:

Generate Random Strings in Excel with VBA

A user wishes to generate random strings of 12 or 15 characters. We can enter all the alphabets, numbers and some special characters into an Excel cell C1:

[email protected]#$%&

Note that we do not use spaces.

We can now use the MID text function in cell D1 to extract a character or a number of characters:

=MID(C1,1,1)

The above MID function will extract the first character ‘A’ from the string in cell C1.

We can also extract ‘abc’ by using the MID function in this manner:

=MID(C1,27,3)

Here we are extracting, starting from the 27th character’ the 3 characters ‘abc’.

We have not yet randomized our extraction. We can do so using the RANDBETWEEN function like this in cell E1:

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

Now the MID function in team with the RANDBETWEEN function will extract 1 random character based on what the RANDBETWEEN function throws up. So if the RANDBETWEEN function extracts the number 3, MID will give us ‘C’ and so on.

Now if we wish to extract 3 characters from our source string we can use the concatenate operator & make our formula like this in cell F1:

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

So if we wished to extract 12 or fifteen characters from our source string we would concatenate our formula 12 or fifteen times using the & operator as follows in cell G1:

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

Also remember that every time you use the above formula you will get a string of 15 characters in cell G1.

In Excel 365 we can use the RANDARRAY function to avoid the repetition of the formula as above. So if write in cell C2 the formula shown below, we will get a 12 character random string every time we use the formula.

=CONCAT(MID(C1,RANDARRAY(1,12,1,LEN(C1),TRUE),1))

CONCAT = Concatenate = &

The RANDARRAY function gives us 1 row of 12 columns of data (string) using 1 as the minimum value and the length of the source string in cell C1 as the maximum value. The ‘TRUE’ converts any decimal number into an integer to be used by the MID function. In this manner we have created a nested function involving ‘CONCAT’, ‘MID’ and ‘RANDARRAY’ functions.

RANDARRAY function used with MID and CONCAT functions
RANDARRAY function used with MID and CONCAT functions to create a Nested function

Now, can we automate the complete process? Yes. Using a macro or VBA code. The complete VBA or macro code is given below and explained in detail in the above video:

Sub GenerateRandomStrings()
Dim sStr As String
Dim i As Long, j As Long
Dim tStr As String

sStr = “[email protected]#$%&”
tStr = “”

For j = 1 To 100
For i = 1 To 15
tStr = tStr & Mid(sStr, Application.WorksheetFunction.RandBetween(1, Len(sStr)), 1)
Next i

Cells(j, 1) = tStr
tStr = “”
Next j

End Sub

Further Reading:

RANDARRAY Function