Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or
0
0
0
s2smodern

## How to generate random character strings in a range in Excel?

Sometimes you may need to generate random strings in cells, such as different passwords. This article tries to show you some tricks to generate different random strings in Excel.

Generate random character strings with formulas

Generate random character strings with VBA code

Easily generate random character strings in selected range:

It is easy to generate random character strings with specific characters and text length with the Insert Random Data utility of Kutools for Excel. See screenshot:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

#### Generate random character strings with formulas

###### Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

The following formulas can help you generate random numbers, letters and alphanumeric values in a range in Excel.

1. To create a random 5-digit number between 10000 and 99999, use this formula: =RANDBETWEEN(10000,99999), 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:

Note: you can change the arguments to get your need.

2. To create a random letter, use the this formula: =CHAR(RANDBETWEEN(65,90)). This formula can generate a random letter from a to z, if you need to create four random letters, you need to use & character to add the letters. Such as =CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)). 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:

Notes:

(1.)The formula is valid in Excel 2007, 2010, and 2013, but not Excel 2003. In Excel 2003, please use the formula =CHAR(INT(RAND()* 25 + 65)) & CHAR(INT(RAND()* 25 + 65)) & CHAR(INT(RAND()* 25 + 65)) & CHAR(INT(RAND()* 25 + 65))

(2.) In formula 65 is A and 90 is Z.

(3.) You can use the character & to add the number of letters you need.

3. To create a random alphanumeric string that has two letters and two numbers, use this formula: =CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99) And you will get the following strings in a range which contain two letters and two numbers:

Note: you can change the arguments to get the digit number as you need, and use & to add the number of letters.

#### 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)
'Update 20131107
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 Function```

3. 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

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.

### Recommended Productivity Tools

#### Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

#### Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

300 New Features for Excel, Make Excel Much Easy and Powerful:

• Merge Cell/Rows/Columns without Losing Data.
• Combine and Consolidate Multiple Sheets and Workbooks.
• Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
• Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
• To post as a guest, your comment is unpublished.
· 1 years ago
I found this useful as a starting point--I hadn't touched VBA in several years, so it was way easier to use the sample code here as a basis than start from scratch.

Skelly1008, have you thought about something like this?

Do
Randomize
charVal = (Int(85 * Rnd) + 48)
If charVal > &H30 And charVal < &H7A Then
If Not (charVal > &H5A And charVal < &H61) Then
If Not (charVal > &H39 And charVal < &H41) Then
newChar = Chr(charVal)
Rand = Rand & newChar
End If
i = i + 1
End If
End If
Loop Until i = getLen

That generates strings that contain only a-z, A-Z, and 0-9.
• To post as a guest, your comment is unpublished.
· 1 years ago
I'm looking for a code that can generate any number 0 to 9 and or any letter A to Z. But I need 25 characters in the that final out put.
• To post as a guest, your comment is unpublished.
· 1 years ago
Found way:
1st: For each char: =RANDBETWEEN(0;1) to randomly select a number or a letter (result in A2 to A9, for example)
2nd: =IF(An=0;RANDBETWEEN(0;9);CHAR(RAND()*26+97)) - in B2 to B9 -> to generate a number or a letter depending on result in A column
3rd: in the cell you want the generated password: =B2&B3&B4&B5&B6&B7&B8&B9
• To post as a guest, your comment is unpublished.
· 2 years ago
I live the formula but once I enter something in another field the numbers in the random fields change. I only want it to randomly generate one time. Not every time I enter data in other fields on the worksheet. Is that a separate function? Thanks!
• To post as a guest, your comment is unpublished.
· 2 years ago
Hi I want one help from you. I have to replace one last three values in this text for example: LoadTesting . I want to change only last three word of it
• To post as a guest, your comment is unpublished.
· 2 years ago
[quote name="rOHIT"]Hi I want one help from you. I have to replace one last three values in this text for example: LoadTesting . I want to change only last three word of it[/quote]
Assuming it is in cell A1:
[code]
=MID(A1,1,LEN(A1)-3)&"CAT"
[/code]