Generate Random Letters

I have previously detailed how to use the RANDBETWEEN function to generate random numbers and dates. This is useful when creating dashboards (when you do not yet have your dataset to work with).

However earlier this week, Chris a reader of this blog wanted to know how to generate random letters.

Well the solution to Chris’s problem is solved using RANDBETWEEN. 

However we want to return the character specified by the code number from the character set on your computer.

	A	B	C
1	Result	Formula	Description
2	M	=CHAR(RANDBETWEEN(65,90))	returns one uppercase letter
3	a	=CHAR(RANDBETWEEN(97,122))	returns one lowercase letter
4	SZ	=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))	returns two uppercase letter
5

Hope this helps Chris. Thanks for stopping by and keep the questions coming…

Generating Random Numbers, Dates or Letters

Quite often I am asked to create dashboards without having the actual data. When I am presented with this dilemma, I will create dummy data to use in lieu of the real data.

To generate numeric and date data, I often will use the =RANDBETWEEN formula.

The different between Excel’s RAND formula and RANDBETWEEN is that you can determine the start and end points for the random number to be generated.

=RANDBETWEEN(Starting Number, Ending Number)

RANDOM NUMBERS

In this example I use the RANDBETWEEN formula to generate numbers between 100 and 500.

Formula	Results
=RANDBETWEEN(100,500)	350
=RANDBETWEEN(100,500)	217
=RANDBETWEEN(100,500)	491
=RANDBETWEEN(100,500)	183

RANDOM DATES

The RANDBETWEEN formula only returns integers so you can’t directly generate dates. However you can add a random number generated with this formula to a date column to generate a date. In this example I generate a random number and add that to the start data of my criteria (1/1/2012). The random number increments the date by the number of days that the random number represents.

Formula for Random Number	Random Number	Starting Date	Random Date	Random Date Formula
=RANDBETWEEN(100,365)	114	1/1/2012	4/24/2012	=C2+B2
=RANDBETWEEN(100,365)	268	1/1/2012	9/25/2012	=C3+B3
=RANDBETWEEN(100,365)	328	1/1/2012	11/24/2012	=C4+B4
=RANDBETWEEN(100,365)	142	1/1/2012	5/22/2012	=C5+B5

You can also use the following formula to pick a random date keyed off of the current date. In this example I am looking for a day within 30 days of the present date:

=TODAY() + RANDBETWEEN(0,30)

=TODAY() - RANDBETWEEN(0,30)

Using the same logic, you can select a date in or within the last year you could use the following formula:

=TODAY() + RANDBETWEEN(0,365)

=TODAY() - RANDBETWEEN(0,365)

RANDOM LETTERS

So how do we generate letters using the RANDBETWEEN formula? Return the Character specified by the code number from the character set on your computer.

=CHAR(RANDBETWEEN(65,90))   - returns one uppercase letter

=CHAR(RANDBETWEEN(97,122))   - returns one lowercase letter

=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))   - returns two uppercase letters

FINAL THOUGHTS

I would also like to point out that the number generated is not truly random but for most cases, can be used for generating generic data.

So how can you use the formula =RANDBETWEEN to help you complete your job?