Posted on Leave a comment

How to generate random numbers in Excel

Here is how you can generate random numbers in Excel:

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

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

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)

 

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

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

Your email address will not be published. Required fields are marked *