Generate random character strings in a range in Excel - A Detailed Guide
Generating random character strings in Excel can be useful for various purposes, including creating unique identifiers, passwords, or sample data. This article provides a comprehensive guide on how to generate random character strings in a range in Excel using different methods.
Generate random character strings with formulas
The following formulas can help you generate random numbers, letters and alphanumeric values in a range in Excel.
Generate random numbers
To create a random 5-digit number between 10000 and 99999, use the following formula, and press Enter key, a 5-digit number will be display in a cell, then select the cell and drag the fill handle across to the range that you want to contain this formula, a range of 5-digit numbers have been generated, see screenshots:
=RANDBETWEEN(10000,99999) |  |  |
Generate random characters
To create random 4 letters, use the following formula. Then you will get a four random letters, and then drag the fill handle over to the range that you want to contain this formula. See screenshot:
=CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))
- CHAR(RANDBETWEEN(65,90)) generates a random uppercase letter;
- &: Concatenates these characters into a single string. Use the & symbol to concatenate as many characters as needed to form longer strings. For example, to generate a string of six random characters, you would extend the formula as follows:
=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))
Generate random alphanumeric strings
To create random alphanumeric strings that has two letters and two numbers, use the following formula. And you will get the following strings in a range which contain two letters and two numbers. See screenshot:
=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)
- CHAR(RANDBETWEEN(65,90)) generates a random uppercase letter;
- RANDBETWEEN(10,99) generates random 2-digit number;
- &: Concatenates these characters into a single string. Use the & symbol to concatenate as many characters as needed to form longer strings.
Generate random character strings with VBA code
If you feel above formulas are difficult and troublesome, the following VBA code can help you much easier. Please do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module window.
Public Function RandomizeF(Num1 As Integer, Num2 As Integer)
'Updateby Extendoffice
Dim Rand As String
Application.Volatile
getLen = Int((Num2 + 1 - Num1) * Rnd + Num1)
Do
i = i + 1
Randomize
Rand = Rand & Chr(Int((85) * Rnd + 38))
Loop Until i = getLen
RandomizeF = Rand
End Function3. Then save and close the code, in a cell, enter this function =RandomizeF(x,y) to insert a random character string with a minimum length of x characters, and a maximum length of y characters.
4. In this example, I will use function =RandomizeF(5,10) to generate a character string which between 5 and 10 characters. Then press Enter key, select the cell and drag the fill handle to the range you want to contain this function. And random of alphanumeric and specific character strings which between 5 and 10 characters have been created. See screenshot:
Generate random character strings with Kutools for Excel
Is there a way to generate random strings with letters, numbers, and special characters, or more? Kutools for Excel's Insert Random Data is an excellent random number (and text string) generator, which can generate random numbers, random text string, or random numbers & text& symbols with all kinds of characters, including letters, numbers, special characters, space, and even custom strings.
1. Select a range in which you will generate random strings, and then click Kutools > Insert > Insert Random Data. See screenshot:
3. In the Insert Random Data dialog box, click String tab, and choose the type of characters as you need, then specify the length of the string in the String length box, and finally click the OK button. See screenshot:
Then the selected range has been filled with random character strings.
Tip: if you want to generate or insert a specified formatting data strings (such as ????@.??.com) in a range, you can also use this utility. Do as this:
1. Select a range and specify the characters and check By mask. Then input the specified data strings you need. See screenshot:
Note: Using ? to indicate a digit of random character in the final specified formatting strings.
2. Then click OK or Apply. The specified formatting data strings have been generated randomly as follows. See screenshot:
Kutools for Excel's Insert Random Data can help you easily process the following operations in a range cells.
- Generate or insert random numbers in a range
- Generate or insert random date in a range
- Generate or insert a custom list in a range
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Generating random character strings in Excel can be efficiently done using formulas or VBA, depending on your needs. By mastering these techniques, you can easily create unique identifiers, passwords, and sample data in Excel. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Demo: Generate random character strings in a range
Related Article:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
