Problem: Instead of random numbers, you need to create random letters.
Strategy: The capital letter A is character number 65. You can use the =CHAR(65) function to produce an A. Thus, to produce a random character between A and Z, you would want to produce a random number between 65 and 90 and use it as the argument to the CHAR function.
Tip If you forget that an “A” is character 65, you can always enter =CODE(“A”) in any cell to learn that the letter “A” is character 65.
To generate random numbers between 65 and 90, follow this logic. First, RAND()*26 will generate numbers between 0 and 25.9999. Taking the integer portion of that function with =INT(RAND()*26) will generate random integers between 0 and 25. Because you want the first character to be an A, you will have to add 65 to the preceding formula.
As shown in Fig. 316, the formula to use is =CHAR(INT(RAND()*26)+ 65).
Additional Details: In many places in this book, I use customers in the form of ABC, Inc. In order to generate these names, I start with a random letter between A and X. Follow these steps.
- In cell G2, enter the formula: =INT(RAND()*24)+65.
-
In cell H2, enter the formula: =CHAR(G2)&CHAR(G2+1)&CHAR( G2+2).
Advertisement -
In a blank area of the worksheet, enter a table with some different company name suffixes.
My table is in K2:K8. Note that each suffix either begins with a comma or a space, as shown in Fig. 317.
There is a function called INDEX, which has three parameters. You might say: =INDEX(SomeRange, WhichRow, WhichColumn). In this case, you always want SomeRange to be $K$2:$K$8. You always want WhichColumn to be 1. You want WhichRow to be a random integer between 1 and 7.
- In cell I2, use this formula: =H2&INDEX(K$2:K$8,INT(RAND()*7) +1,1).
- Copy G2, H2, and I2 down to as many rows as you need data.
Result: As shown in Fig. 318, you have a column of random company names. You can safely use these in a book without infringing any trademarks.
Summary: The RAND() function can generate random letters as well as numbers when it is used in conjunction with the CHAR function.
Functions Discussed: =CHAR(); =CODE(); =INDEX(); =RAND()
Images
