Create Random Text in Excel

Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.

I needed 2 types of data:

  • Numbers: sample test scores in one column
  • Text: random Region names and Gender in other columns.

Create random text in Excel http://blog.contextures.com/

Create Random Numbers

It’s easy to create random numbers in Excel – just use the RANDBETWEEN function. Set a bottom and top number, and a random number from that range is returned. I wanted test scores between 30 and 100, so I used this formula:

=RANDBETWEEN(30,100)

chooserandomtext02

Get Random Text

The RANDBETWEEN function can only return numbers, so it’s a little trickier to create a set of random text data. I’ve tried different ways in the past, and they usually involve multiple steps. It’s nothing too complicated, but it takes longer than just using RANDBETWEEN.

This time, I tested a new method – using RANDBETWEEN inside a CHOOSE formula. Both lists of options were short – 2 genders and 3 regions – so they would work nicely as values in a CHOOSE formula.

chooserandomtext03

Type the List of CHOOSE Values

In my Gender formula, there would be two values, so the RANDBETWEEN function used 1 as the bottom value and 2 as the top.

Then, I typed in the Genders, as the values, putting double quote marks around each value.

=CHOOSE(RANDBETWEEN(1,2),”Male”,”Female”)

Create random text in Excel http://blog.contextures.com/

Use Cell References as CHOOSE Values

In my Region formula, there are 3 values, so the RANDBETWEEN function used 1 as the bottom value and 3 as the top.

Instead of typing the region names, I used absolute references to the cells which contained the region names. Cell references are a better choice if the values are long, or if they’ll change frequently. Also, it’s quicker than typing!

=CHOOSE(RANDBETWEEN(1,3),$K$4,$K$5,$K$6)

Create random text in Excel http://blog.contextures.com/

Paste As Values

The final step in setting up the random data is to copy the columns, and paste them as values. That will lock in the values, so they don’t keep changing, as you work in the file.

  • I use the mouse to select the columns, then drag to the right, while holding the mouse button.
  • Drag the column back to its original location, and let go of the mouse button.
  • Then, in the popup menu, click on Copy here as Values Only

You can see that step near the end of the video, below.

Video: Create Random Text With CHOOSE

Watch this video to see the steps for using RANDBETWEEN on its own, and combined with CHOOSE.

Or watch on YouTube: Create Random Text in Excel With CHOOSE

Download the Sample File

To download the sample file, go to the Excel Sample Files page on my website, and in the Functions section, look for FN0035 – Create Random Text with CHOOSE Function. The file is in xlsx format, and does not contain macros. One sheet has the formulas, and the other has the data pasted as values.

_________________

You may also like...

9 Responses

  1. aMareis says:

    alternative Region formula
    =INDEX($K$4:$K$6, RANDBETWEEN(1,3))

  2. MF says:

    Hi Debra,
    I really like the trick in converting formula into value by RIGHT-CLICK mouse dragging. To me, it’s a new discovery. ;p As I find it very handy to swap cells in a table without affecting other cells outside the table. I am going to write a post about it and will share with you.
    On the other hand, I prefer using INDEX(Array,RANDBETWEEN(1,100)) if we are having a long long list.
    Cheers,
    MF

  3. @MF and @aMareis — Thanks, and you’re right — INDEX with RANDBETWEEN is much better for a list of items.

  4. AlexJ says:

    An alternate approach that I use is to NOT use random numbers. Instead, the values and text follow a pattern (e.g. Person1, Person2, …, 10001,10002, 10003,…) That way,it’s easier to debug missing items, spot patterns in the results and deconstruct calculations.

  5. Skater says:

    Ever try pwrrandfromrange()? It would look like =pwrrandfromrange(k4:k6) with your example above.

  1. August 25, 2014

    […] Create Random Text in Excel […]

Leave a Reply

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