In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.

On a small scale you can use the CHOOSE function to select from a limited number of entries. If you need to select from a larger group of text entries then you can use the INDEX function.

CHOOSE Method

The CHOOSE function method involves using the RANDBETWEEN function to generate a number and then listing the possible entries in the CHOOSE function.

See example below – the formula in cell A2 is

=CHOOSE(RANDBETWEEN(1,5),"John","Tom","Sue","Jan","Dan")

The RANDBETWEEN function generates a random number from one to five. This is then used by the CHOOSE function to select one of the five names listed in the rest of the CHOOSE function. If the number generated was 2, it would select Tom, the second name listed. Obviously this technique is not scalable for large numbers of text entries. To handle more text entries review the INDEX method below.

INDEX Method

To create a scalable method of randomly choosing text entries from a large list you can use the INDEX function. This technique involves creating a list of possible text entries and then using the INDEX function with the RANDBETWEEN function to randomly select an entry from the list.

The image below has a listing of 10 last names. These are in a sheet called Names.

The formula in the image below in cell B2 is

=INDEX(Names!A:A,RANDBETWEEN(1,COUNTA(Names!A:A)))

Note: the first names shown above in column A in the listing are changing because the formulas recalculate each time Excel calculates.

The RANDBETWEEN function will generate random numbers between 1 and the number of names in the list in column A of the Names sheet. Using the COUNTA function makes this dynamic as it counts the number of names in the list and uses that as the upper limit for the RANDBETWEEN function. Adding names to the list will automatically increase the number of random numbers possible. This assumes no blanks in the names list and no other entries below the names listed.

As with generating random numbers, you may want to use Copy and Paste Special > Values to capture the entries so they don’t change each time the file calculates.