How to generate random string from a set of available strings in Excel?

I want to generate a column which will have random string values in any order. Those string values have to be from ('New', 'Mint', 'Very Good', 'Good', 'Acceptable', 'Poor') strings? Can anyone let me know about how can I make use of `RANDBETWEEN` function to populate my columns with the mentioned data in brackets?

• Can you repeat those strings or can they only appear once? Nov 12, 2015 at 15:46
• I have to repeat those strings Nov 12, 2015 at 15:50

If you only have a few strings

If your strings are in the first column you can use the `CHOOSE()` function like this:

``````=CHOOSE(RANDBETWEEN(1,6),\$A\$1,\$A\$2,\$A\$3,\$A\$4,\$A\$5,\$A\$6)
``````

`RANDBETWEEN(1,6)` will randomly pick a number n from 1 to 6 - `CHOOSE` then counts through all the parameters you've listed (your strings in column A) and displays the nth term.

But, if you have many strings

If you have more than a few items to `CHOOSE` from, maintaining this formula becomes unwieldy.

In which case it's probably a good idea to insert a Table (let's call it MyStrings), create an ID column in column A using `=ROW()-1` and switch our formula to a `VLOOKUP`. We can use `MIN` & `MAX` and structured references to dynamically work out how many terms there are in your table, for use in the `RANDBETWEEN`:

``````=VLOOKUP(RANDBETWEEN(MIN(MyStrings[ID]),MAX(MyStrings[ID])),MyStrings,2,0)
``````

Careful Now

`RANDBETWEEN` is a volatile function, which means that it recalculates every time any other cell changes (anywhere in your workbook). If you need to keep the randomly-generated strings fixed as they are once you've produced them, you'll need to copy and Paste Special as Values.

If you use a lot of volatile functions, it may slow your worbook down, but there are things you can to do alleviate this.

You can use `INDEX` together with `RANDBETWEEN` to do this.

``````=INDEX({"New","Mint","Very Good","Good","Acceptable","Poor"},RANDBETWEEN(1,6))
``````

Just fill this formula down your column.

• is this volatile? Nov 12, 2015 at 18:37
• @Raystafarian yes, because of `RANDBETWEEN` the value will change each time the sheet is calculated. Nov 12, 2015 at 18:54
• I thought so, but nobody mentioned it to the OP. Nov 12, 2015 at 18:55
• Good point. I'll edit my post to make that point as well. Nov 13, 2015 at 9:28