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.
Can you repeat those strings or can they only appear once? – Andi Mohr – 2015-11-12T15:46:55.713
I have to repeat those strings – Ashutosh – 2015-11-12T15:50:28.720