One of the coolest features in Excel is generating random text. You can generate a random letter or text using the RANDBETWEEN function. Using the RANDBETWEEN and CHOOSE functions, you can generate random text from a list. In this tutorial, you will learn how to generate random text values from a list.
How to Generate Random Text Values from a List in Excel
You can nest the RANDBETWEEN function inside the CHOOSE function to generate random text values. The RANDBETWEEN function generates an arbitrary number from a top and bottom value. It uses the syntax,
=RANDBETWEEN(bottom, top) where the bottom value is the lowest value of the range and top is the highest value in the range.
However, it cannot return text values. To overcome this issue, you need the CHOOSE function. The CHOOSE function in EXCEL returns a value from a given list using an index that is specified. It uses the syntax,
=CHOOSE(index, val1, [val2], …) where index is the position in the list where the value will be extracted from. Val1, [val2], … is the list that is used.
The following example contains the data for student assignments. The first column has the student names and the second column has the books they need to use for the assignment. The data contains five students and three books: Wuthering Heights, Dracula, and The Alchemist. To assign the books randomly among the students:
- Go to cell C2. Click on it with your mouse to select it.
- Assign the formula,
Wuthering Heights", "Dracula", "The Alchemist")to C2.
- Press Enter to apply the formula to C2.
- Drag the formula all the way from C2 to C6 with your mouse.
This will show the student names and the corresponding books assigned randomly among the students.