How to generate random letters in Excel

Excel provides three functions for generating random values: RAND(), RANDARRAY(), and RANDBETWEEN(), but there’s no comparable function for generating random letters. The truth is, most of won’t need to generate random numbers, but if you do, you want to do so quickly, right? Fortunately, by combining a couple of functions, you can generate random letters. In this article, I’ll review those two functions and then show you how to combine them to get quick results.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions through Excel 2007. For your convenience, you can download the demonstration .xlsx and .xls files. Excel Online supports these functions.

About RANDBETWEEN() and CHAR() in Excel

You’re probably familiar with both of these functions already, but it might never occur to you to use them together. Fortunately, they play nicely together.

First, let’s review RANDBETWEEN(). This function returns a random integer value between two specified values, every time the sheet is calculated. That last part is important, and we’ll get back to that later. Its syntax,

RANDBETWEEN(bottom, top)

has two required arguments: Bottom specifies the small integer that can be returned, and top specifies the largest integer that can be returned.

CHAR() returns a character specified by a value, so it’s good for translating, which is how we’ll use it. Its syntax,

CHAR(value)

has only one argument, which in this case, will be a value between 1 and 255. Those values represent characters in the system’s character set, which was originally based on ANSI and for our purposes in Excel is ASCII decimal. It’s doubtful you will need that information, but it doesn’t hurt to know about it.

Figure A shows a sheet of characters returned by the CHAR() function using the values 65 through 90 and 97 through 122. We’re not concerned with the characters returned by any other value.

Figure A

Can you see where we’re headed?

How to combine the two

At this point, we have a function that returns random numbers between two specified values and a function that converts values to alphabetic characters. By combining them, we can create a function that returns random letters.

To return a list of random upper-case and lower-case letters, enter the following functions and copy at will:

B2: =CHAR(RANDBETWEEN(65,90))

C2: =CHAR(RANDBETWEEN(97,122))

Figure B shows my lists. Your lists won’t match mine because the results are random.

Figure B

You can change the bottom and top values to reduce the number of letters returned. For example, if you wanted to return random letters between e and j, you’d use the expression:

=CHAR(RANDBETWEEN(101, 106)

First, the RANDBETWEEN() function returns a random letter between bottom and top. Then, the CHAR() function translates that value into a character, in this case, letters of the English alphabet.

Returning a single random letter or a series of single random letters is fairly easy once you know how to combine CHAR() and RANDBETWEEN(). How would you return a random set of more than one letter?

How to concatenate results in Excel

If you want a series of random strings, as shown in Figure C, simply concatenate two functions. Let’s suppose you want the first letter to be uppercase and the second to be lowercase. In this case, you’d use the following function:

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))

Figure C

The first combined function returns a random uppercase letter and the second returns a random lowercase letter. The & character is a concatenation operator. It simply combines the results of both functions; it doesn’t total values.

You could combine several combinations for all kinds of strings.

How to convert to literal values in Excel

Earlier, I mentioned that RANDBETWEEN() calculates every time you calculate that workbook. That means every time you enter anything or press F9, these functions return different characters. That might not matter, but if it does, you’ll want to convert the results into literal values. To quickly convert functions to their literal values, do the following:

1. Select the values.
2. Press Ctrl + C to copy them to the Clipboard.
3. In the Clipboard group (on the Home tab), click the Paste dropdown and choose Values (V) in the Paste Values section. Doing so will replace the functions with their results.

Stay tuned

This simple combination yields flexible results—you can randomly return as many letters as you need. In a future article, we’ll use RANDBETWEEN() to generate random content.