Excel has no function for random letters, so pair RANDBETWEEN with CHAR: =CHAR(RANDBETWEEN(65,90)) returns a random capital letter, and =CHAR(RANDBETWEEN(97,122)) a random lowercase one. Those numbers are the character codes — 65 is A, 90 is Z, 97 is a, 122 is z.
For a mix of both cases, add 32 at random: =CHAR(RANDBETWEEN(65,90)+32*RANDBETWEEN(0,1)), since lowercase sits exactly 32 codes above uppercase. To restrict the range, narrow the numbers — =CHAR(RANDBETWEEN(65,71)) gives A to G only.
For a specific set of letters rather than a range, list them in a column and pick one at random: =INDEX($A$1:$A$4,RANDBETWEEN(1,4)). To build a code, chain them: ="CHD"&RANDBETWEEN(100,999)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90)) produces things like CHD417KP.
One thing to remember: RANDBETWEEN is volatile, so the letters change on every recalculation — once you’re happy, copy the column and paste it as values (Ctrl+Alt+V → V) to freeze them.
Ever faced a situation where you need to generate random letters? Well, sometimes you do need to generate some random alphabets.
Right? In Excel, you have functions like RAND and RANDBETWEEN to generate random numbers, but we don’t have any particular function to generate letters.
Today, I’d like to share with you an amazing formula trick to generate random letters in Excel. And, the best part is it’s simple and easy to use.
So what’s the Formula to Generate Random Letters in Excel?
As I said, there is no direct function in Excel that can help you to get random letters/alphabets. But, you can use a combination of RANDBETWEEN and CHAR to create a formula to get random letters.
If you want to Generate CAPITAL Random Letters
In this formula, you need to use 65 and 90 for the random letter to be a capital letter.
=CHAR(RANDBETWEEN(65,90))
Or if you want to Generate Small Random Letters:
In this formula, you need to use 97 and 122 for the random letter to be a small letter.
=CHAR(RANDBETWEEN(97,122))
How this formula works
Now, let me tell you how this formula generates these letters. First of all, you have to understand that this formula works in two different parts.
In the first part, you have the RANDBETWEEN function which can generate random numbers for you. You just have to specify the lowest number and the highest number, and it will return a random number from that range. In short, you will get a random number in the result within the range.
Now, in the second part, you have the CHAR function, which can return a specific character when you specify a number in it. That means for each character, there is a specific number you have to insert in the CHAR function. Numbers 65 to 90 represent capital letters, and 97 to 122 represent small letters.
If you enter 65 in CHAR, it will return “A”, and for 97, it will return “a”.
The Bottom Line is:
- When you specify any of the above ranges, RANDBETWEEN returns a random number.
- And after that, CHAR returns a character for that number.
This is the whole story.
Generate a Random String of Letters
To get more than one letter in a cell, join the same formula a few times with the ampersand. The following formula gives you three random capital letters, like “NHS”:
=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
In Excel 365 and Excel 2021, you can use RANDARRAY to create any number of letters in one go. The following formula gives you a string of 8 random capital letters:
=CONCAT(CHAR(RANDARRAY(8,1,65,90,TRUE)))
And if you want a code with a letter and numbers, like “J722”, add RANDBETWEEN for the numbers:
=CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,999)
Random Letters from a Specific Range
You can change the two numbers in RANDBETWEEN to get letters from a part of the alphabet only. For example, 65 to 69 gives you a random letter from A to E, which is handy for random grades or groups:
=CHAR(RANDBETWEEN(65,69))
To find the number for any letter, use the CODE function, like =CODE(“E”), which returns 69.
Stop the Letters from Changing
RANDBETWEEN is a volatile function, so the letters change every time the worksheet recalculates, for example, when you enter something in any cell. To keep the letters you have, copy the cells and paste them as values (Home ➜ Paste ➜ Values).
Frequently Asked Questions
Can I generate random letters without repeats?
In Excel 365 and 2021, yes. Use =CHAR(64+TAKE(SORTBY(SEQUENCE(26),RANDARRAY(26)),5)) to get 5 different letters. In older versions, put A to Z in a column, add =RAND() next to it, and sort by that column.
Why do my random letters keep changing?
Because RANDBETWEEN recalculates every time the sheet changes. Paste the cells as values once you have the letters you need.
Can I mix capital and small letters?
Yes. Pick the case at random as well, with =IF(RAND()<0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))).
In the end,
The formula you have used above is simple and easy to apply; there’s no doubt about it. The best real-world use of this method that I have found is to generate random groups for participants based on the alphabet.
Do you have any other method to create random letters?
Make sure to share your views with me in the comment section. I’d love to hear from you, and please don’t forget to share this post with your friends; I am sure they will appreciate it.
Hey sir thanks you alot for you explain. I’d like to ask you how can I write a random letters just in blank cells
Hi,
I want to randomize in a range of cells in a column, the letters S C and Q. How can I do this?
If you want a mix of upper and lower case then you can use:
CHAR(RANDBETWEEN(65,90)+32*RANDBETWEEN(0,1))
This works because upper case letters start at 65 and lower case letters start at 97, a difference of 32.
RANDBETWEEN(65,90) will generate a random upper case letter code, and the 32*RANDBETWEEN(0,1) will randomly add 0 or 32 to the letter code.
If it adds 0 then it will remain upper case, if it adds 32 then it will switch it to lower case.
Great job Alex. Works great with uppercase and lowercase letters. How can I add more numbers (0-9) without symbols so that it is taken into account in one formula. I mean iteration A-Z, a-z, 0-9. Thank you.
how can we create unique code there is including numbers and alphabetics excluding
A,S,T,M …???????????????
@Aakil
You could put all the letters you want in column A, e.g.:
A
S
T
M
Then you could use
=INDEX(A:A,RANDBETWEEN(1,4))
If you have more than 4 letters to choose from, then increase the ‘4’ in the formula to match.
I want my random numbers to only be from ‘A’ to ‘G’ What can I do
Based upon the example provided above you could try =Char(RandBetween(65,71)
What if I want the first few letter to always be the same? Then say three random number and letters
So CHD725KLH, CHD128DHL, CHD631PXY as an example
Hi, Christina,
try this:
=”CHD”&CHAR(RANDBETWEEN(48,57))&CHAR(RANDBETWEEN(48,57))&CHAR(RANDBETWEEN(48,57))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
P.S. Thanks, Puneet, for interesting articles. Good job 🙂
Hi Christiana,
Try this too
=”CHD”&RANDBETWEEN(100,999)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
If I want to generate a series of A’s and B’s but never more than 2 of A or B is it possible?
Didn’t get you properly. Can you come again?
Thank You Sir,
You’re welcome. 🙂
Thanks again Puneet
You’re welcome.
To keep within Windows Rules something a formula like
=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(48,57))&CHAR(RANDBETWEEN(33,47))&CHAR(RANDBETWEEN(33,126))&CHAR(RANDBETWEEN(33,126))&CHAR(RANDBETWEEN(33,126))&CHAR(RANDBETWEEN(33,126))&CHAR(RANDBETWEEN(33,126))
would be useful
Thanks for sharing, Andrew.
Interesting piece. Please what if I want to generate 5 alphabets and the result to be in one single cell like “B, E, H, P, G”. Do I have to write the formula above and concatenate/ampersand them or is there a shorter and better means? Thank you
You can use VBA for that. tell me If I can help.
Yes please, I will appreciate
Hello.
May be this is useful for you.
=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
Awesome man, thank you !
I so glad you liked it. 🙂