Do you ever need to generate a column of sample text? Do it in Excel pretty quickly.
Text list in Excel
Here's a technique I just used to create sample data for a cool tutorial I'm working on. Let's say you want a hundred cells down a column to contain one of five words or phrases and you want the list randomized. Here's how to do it by nesting the Randbetween function inside the Choose function.
Randbetween generates random numbers between a lower and upper range. For example:
=RANDBETWEEN(5,15) ...returns any integer from 5 through 15.
The Choose function has two types of arguments: an index number and a list. The function returns the list item that corresponds to the index number. For example:
=CHOOSE(2, "Apples", "Bananas", "Pears", "Oranges", "Peaches") ...returns "Bananas" because it's the 2nd item in the list. Because these are text items, they need to be in quotation marks.
If we want to generate 100 cells that has the entries in the above list randomized, we use Randbetween to generate the index number.
Keep in mind this list will keep changing since the entries are based on a random number that changes every time something happens on the workbook. If you want to convert the formulas into actual text:
1. Copy the column to the clipboard.
2. On the Home tab of the Ribbon, click the down arrow on the Paste button and choose Paste Values.
Comments (0)