In Google Spreadsheet, how do I make cells take a random string value from a list of strings?

Question

Suppose I have a list of strings:

apple, pear, banana, orange

How can I have each cell in a column of cells have a random value from that list?

Answer 1

Please try:

=index(A:A,randbetween(1,4))  

where your list of fruits is assumed to be in ColumnA (and consists of four items).

Or without a separate list:

=choose(randbetween(1,4),"apple","pear","banana","orange")

Answer 2

Another way, which I prefer, is:

=INDIRECT("A"&RANDBETWEEN(3,7))

Where "A" is the column, and 3,7 is the start and end rows for your list. This way you don't have to figure out how many elements are in your list, you just look at the start and end row numbers, and you don't have to use the whole column for your list. You can have headers and such.

You also can modify this to make it more maintainable, by substituting the last number with the COUNTA function and adding the offset:

=INDIRECT("A"&RANDBETWEEN(4,COUNTA(A4:A99)+3))

The 4 is the first row of the list. The +3 should be 1 less than that number to skip rows not in the range of the list. Give COUNTA a large enough range that you won't have to worry about it for a while, then you can just add items to your list and they'll already be included in your random selection without needing to do anything else. Just ensure the range only includes your list and empty cells.

Answer 3

The other answers are fine but an easier solution is our add-on: https://gsuite.google.com/marketplace/app/random_data_generator/626703404158

You can choose from your own data, strings, numbers, user agents, and many other random datatypes.