How to Generate Random Letters in Excel?

by

When working with Excel, I often have a need to generate random data for various purposes.

One interesting task that comes to mind is generating random letters in Excel.

This can be helpful for creating sample text, testing formulas, or even generating security keys and passwords.

Although Excel has two worksheet functions, RAND and RANDBETWEEN, meant for generating random numbers, it does not have specific functions for generating random alphabet letters.

But there is a simple workaround that will allow us to quickly generate a list of random letters or even a string of letters.

In this tutorial, I will show you two workarounds to generate random letters in Excel. I have also covered a method that shows how to generate a string of random letters in Excel.

Generate Random Letters in Excel (With Duplicates)

Let’s cover the following:

  1. How to Generate Random Letters in Uppercase
  2. How to Generate Random Letters in Lowercase

The RANDBETWEEN function produces a random number between specified numbers.

The CHAR function returns the letter identified by the code number.

Therefore, we can use a formula where the numbers produced by the RANDBETWEEN function are used as arguments by the CHAR function to generate random letters in either uppercase or lowercase.

How to Generate Random Letters in Uppercase

I want to generate 15 random letters in uppercase using a formula combining the CHAR and RANDBETWEEN functions. 

Here is how I do it:

  1. Select cell A1 and enter the following formula:
=CHAR(RANDBETWEEN(65,90))
CHAR and RANDBETWEEN formula to generate random letters in uppercase
  1. Drag the Fill Handle to copy the formula down the column to cell A16.

Fifteen random letters in uppercase are generated.

random letters generated in a column in Excel

Note: The volatile formula will generate new random letters each time you recalculate the worksheet. If you want to freeze the values, copy and paste the cells as values.

Explanation of the formula

=CHAR(RANDBETWEEN(65,90))

This formula uses the RANDBETWEEN function to generate random numbers between and including 65 and 90 and then uses the CHAR function to convert the numbers to letters in uppercase.

Note that 65 is the ASCII code for “A,” and 90 is the ASCII code for “Z.

Also read: How to Change Uppercase to Lowercase in Excel

Handling Duplicate Letters

Notice that the formula combining the CHAR and RANDBETWEEN functions returned a couple of duplicate letters, as seen below:

There are duplicates in the random letters generated by a formula in Excel

If you have no problem with duplicate values, the formula is the most convenient way to generate random letters in uppercase in Excel. 

However, if you do not want duplicate values in the dataset, you can remove them using Excel’s built-in Remove Duplicates feature.

Here is how to do this:

Remove the formulas and only keep the values
  • On the Data tab, on the Data Tools group, click the Remove Duplicates button.
click on the remove duplicate cycle in the ribbon in Excel
  • On the Remove Duplicates dialog box, ensure the target column is selected and click OK.
select the column name from which you want to remove the duplicate random letters

Excel sends a message box to the screen, showing the number of duplicate values found and removed and the number of remaining unique values.

excel shows a message mentioning the number of duplicate cells it has removed

Check the dataset to confirm that the duplicate values have been removed and that all the remaining letters are unique.

random letters data set in a column where the duplicates have been removed
Also read: How to Generate Random Numbers in Excel (Without Duplicates)

How to Generate Random Letters in Lowercase

To generate, for example, fifteen random letters in lowercase, we follow the steps below:

  1. Select cell A1 and enter the below formula:
=CHAR(RANDBETWEEN(97,122))
formula to get random letters in lower case in Excel
  1. Copy the formula down the column by dragging the Fill Handle to cell A16.
copy the formula down to random letters in lower case in XL

Note that the formula is volatile and will generate new random letters each time you recalculate the worksheet. If you want to freeze the values, copy and paste the cells as values.

Notice that the formula returned some duplicate letters, as seen below:

the result of the formula would have some duplicate letters

If you are OK with duplicate values, the formula is the most convenient way to generate random letters in lowercase in Excel. 

However, suppose you do not want duplicate values in the dataset. In that case, you can remove them using Excel’s built-in Remove Duplicates feature, as described in this tutorial’s “Handling Duplicate Letters” section.

Explanation of the formula

=CHAR(RANDBETWEEN(97,122))

This formula uses the RANDBETWEEN function to generate random numbers between and including 97 and 122 and then uses the CHAR function to convert the numbers to letters in lowercase.

Note that 97 is the ASCII code for the letter “a,” and 122 is the ASCII code for the letter “z.”

Also read: How to Extract Number from Text in Excel (Beginning, End, or Middle)

How to Generate a String of Random Letters in Excel

You can generate a string of random letters in Excel, not just individual ones.

Let’s look at the following:

  1. How to generate a string of random letters in uppercase.
  2. How to create a string of random letters in uppercase and lowercase.

Generate a String of Random Letters (Uppercase)

We use the steps below to generate a string of five letters in uppercase:

  1. Select the cell to which you want to add a string of random letters in uppercase and type in the following formula:
=TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,65,90,TRUE)))
formula to generate a string of random letters in upper case
  1. Press Enter.
press enter to get the result of the formula

The formula generates five random letters in uppercase. 

Note that the formula is volatile and generates a new string of random letters each time you recalculate the worksheet. If you want to freeze the string, copy and paste it as a value.

Explanation of the formula

=TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,65,90,TRUE)))

  • =RANDARRAY(1,5,65,90,TRUE) The RANDARRAY function is the innermost in the formula, and it returns a row of five integers between 65 and 90.
rand array function generates an array of random numbers

Note that 65 is the ASCII code for “A,” and 90 is the ASCII code for “Z.”

  • CHAR(RANDARRAY(1,5,65,90,TRUE)) The CHAR function is the second innermost function in the formula, and it converts the five integers returned by the RANDARRAY function to five uppercase letters.
using the char function then then generates random letters array in upper case
  • Finally, the TEXTJOIN function joins the five letters returned by the CHAR function into a string of random letters in uppercase.

Generate a String of Random Letters (a Mix of Uppercase and Lowercase Letters)

You may want to generate a string with a mix of random uppercase and lowercase letters.  

We use the steps below to generate a string of ten random letters, five in uppercase and five in lowercase:

  1. Select the cell to which you want to add a string with a mix of random lowercase and uppercase letters and type in the following formula:
=TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,65,90,TRUE)))&TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,97,122,TRUE)))
formula to generate a string of random letters in upper and lower case
  1. Press Enter.
Result of the formula that generates random letters in Upper and lower case

The formula generates a string of ten random letters, five in uppercase and five in lowercase.

Explanation of the formula

=TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,65,90,TRUE)))&TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,97,122,TRUE)))

  • =TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,65,90,TRUE))) This first part of the formula generates five random uppercase letters. Note that the value 65 is the ASCII code for “A,” and 90 is the ASCII code for “Z.”
  • TEXTJOIN(,TRUE,CHAR(RANDARRAY(1,5,97,122,TRUE))) This second part of the formula produces five random lowercase letters. Note that 97 is the ASCII code for “a,” and 122 is the ASCII code for “z.”
  • Finally, the ampersand operator (&) joins the five random uppercase letters to the five random lowercase letters.

Note: The formula is volatile and generates a new string of random letters each time the worksheet recalculates. If you want to freeze the string, copy and paste it as a value.

Also read: How to Remove Commas in Excel (from Numbers or Text String)

Situations Where Generating Random Letters Could Be Useful

Generating random letters in Excel can be helpful in different scenarios.

Here are some examples:

  • Placeholder data: When creating mock data for a presentation or demonstration, you may need to fill a column with random letters as a placeholder for data that you will enter later.
  • Testing: If you need to test a formula that requires the input of letters, you can generate random letters to test the formula.
  • Unique identifiers: You can use random letters as unique identifiers or codes for tracking and organizing data, for example, when creating unique IDs for a large dataset.

This tutorial showed three techniques for generating random letters in Excel.

One method explained how to generate random letters with duplicates, and the other showed how to generate random letters without duplicates.

The third method showed how to generate a string of random letters in Excel.

Each method has its advantages and disadvantages, and the best technique for generating random letters in Excel will depend on your specific needs and preferences.

We hope you found this Excel tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment