Here is how you can generate random numbers in Excel:

Put all your possible characters into an individual cell, such as A1:

1 | ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvxyz0123456789 |

You can name this cell *MySource*. You could then use a formula such as the following to return the random string of characters (broken into individual lines for clarity, but it is still a single formula):

1 2 3 4 5 6 7 | =MID(MySource,RANDBETWEEN(1,LEN(MySource)),1)&MID(MySource,RANDBETWEEN(1,LEN(MySource)),1)& MID(MySource,RANDBETWEEN(1,LEN(MySource)),1)& MID(MySource,RANDBETWEEN(1,LEN(MySource)),1)& MID(MySource,RANDBETWEEN(1,LEN(MySource)),1)& MID(MySource,RANDBETWEEN(1,LEN(MySource)),1)& MID(MySource,RANDBETWEEN(1,LEN(MySource)),1)& MID(MySource,RANDBETWEEN(1,LEN(MySource)),1) |

This long formula concatenates eight characters pulled from the cell source entered in A1

There is another approach for this that uses a table that contains all the characters you want in your random text string. Start by placing the number 1 through 62 in a column, one number in each row. To the left of these numbers place your other charactheres – A, B, C, D, E etc. (this should be the same characters you placed in cell A1, using the previous method).

Select both columns of the 63 rows and name it *MyTable*. You can then use the following formula to generate the random characters (broken into individual lines for clarity, but it is still a single formula)

1 2 3 4 5 6 7 8 | =VLOOKUP(RANDBETWEEN(1,62),MyTable,2)& VLOOKUP(RANDBETWEEN(1,62),MyTable,2)& VLOOKUP(RANDBETWEEN(1,62),MyTable,2)& VLOOKUP(RANDBETWEEN(1,62),MyTable,2& VLOOKUP(RANDBETWEEN(1,62),MyTable,2)& VLOOKUP(RANDBETWEEN(1,62),MyTable,2)& VLOOKUP(RANDBETWEEN(1,62),MyTable,2)& VLOOKUP(RANDBETWEEN(1,62),MyTable,2) |

Attention: Both approaches have one drawback – they are regenerated each time the worksheet is calculated making it impossible to have a single generated random string that will not change on a regular basis. The best way around this is to use a macro that will put the random strings into your workbook starting at a specific cell location

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | Sub MakeRandom() Dim J As Integer Dim K As Integer Dim iTemp As Integer Dim sNumber As String Dim bOK As Boolean Range("D4").Activate Randomize For J = 1 To 50 sNumber = "" For K = 1 To 8 Do iTemp = Int((122 - 48 + 1) * Rnd + 48) Select Case iTemp Case 48 To 57, 65 To 90, 97 To 122 bOK = True Case Else bOK = False End Select Loop Until bOK bOK = False sNumber = sNumber & Chr(iTemp) Next K ActiveCell.Value = sNumber ActiveCell.Offset(1, 0).Select Next J End Sub |

Run the macro, and whatever is in cells D4:D53 is overwritten by the random values. If you want the values written into a different location, change the *Range* statement near the beginning of the macro.

If you want to try it you can get it from my Skydrive: Download Random Numbers Generator

## Leave a Reply