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

or

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 with Kutools for Excel


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. Download the full feature 60-day free trail of Kutools for Excel now!

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

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Generate random character strings with formulas

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:

doc-generate-strings1 -2 doc-generate-strings2

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:

doc-generate-strings3

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:

doc-generate-strings4

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:

doc-generate-strings5


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.

Kutools for Excel, with more than 120 handy functions, makes your jobs easier. 

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.


Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel



Related Article:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Carl · 2 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.
    skelly1008 · 2 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.
    Nuno Figueiredo · 2 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.
    JoJo · 3 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.
    rOHIT · 3 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.
      Jingle · 3 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]
  • To post as a guest, your comment is unpublished.
    Philip · 3 years ago
    Hi,

    On generating the random number in excel,can we insert the value to a form?any query plz share
  • To post as a guest, your comment is unpublished.
    Samantha R. · 3 years ago
    This is several times better than I dared hope for!! I completely disregarded the idea of generating several values in a single cell. Guaranteed I'll be using this info every chance I get! 11/10.
  • To post as a guest, your comment is unpublished.
    simone · 4 years ago
    Hi i would like to make the same in a MS WORD tab. is it possible ?
  • To post as a guest, your comment is unpublished.
    Adam · 4 years ago
    The VBA isn't working as a true random number. I'm using (10,12) as the criteria and if I run it down a couple thousand rows and do a countif formula in the adjacent column I find many duplicate passwords.
  • To post as a guest, your comment is unpublished.
    Carly · 4 years ago
    Very helpful, thank you.
    Using the VBA code, is it possible to only show letters and numbers and not other symbols?
  • To post as a guest, your comment is unpublished.
    Valentino · 4 years ago
    it works :)
    But every time he is generating new passwords, if i select or generate on another cell, previewed ones are changing all the time, and i want to not to change them. please help
  • To post as a guest, your comment is unpublished.
    curtis · 5 years ago
    Is there a way to keep the values from changing every time I change a different field in the sheet? The passwords re-randomize every time something is changed in the sheet at all.
  • To post as a guest, your comment is unpublished.
    mel · 5 years ago
    hi,
    I need to create unique codes - how can I test to ensure the codes are not duplicated?
  • To post as a guest, your comment is unpublished.
    lineth peou · 5 years ago
    thank you for posting use full formula.
  • To post as a guest, your comment is unpublished.
    Anil Choudhary · 5 years ago
    How to do the randbetween for jan to dec string.
  • To post as a guest, your comment is unpublished.
    Crystal · 5 years ago
    VBA method worked great. Thanks so much. I really needed this!
    Take care...
  • To post as a guest, your comment is unpublished.
    Dan88 · 5 years ago
    I was wondering if there was a way to add a generic word to the beginning of each generated code, i.e. DISCOUNT56979, DISCOUNT88531, etc. The reason I ask is so that I can track discounts used from different leads.
    • To post as a guest, your comment is unpublished.
      HelloKelly · 5 years ago
      In the Insert Random Data dialog box, there is a [b]By mask[/b] option on the String tab that can help you.

      For example, Add the “DISCOUNT” before the 5 random characters, please check the [b]By mask[/b] option, and then enter the “[i][b]DISCOUNT?????[/b][/i]” in the following box.
      • To post as a guest, your comment is unpublished.
        RRK · 4 years ago
        Try This: ="DISCOUNT"&RANDBETWEEN(10000,99999)
        This Code is for 5 Random numeric character after "Discount"

        For alphanumeric try this:
        ="DISCOUNT"&CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(0,9)

        or

        ="DISCOUNT"&CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)
  • To post as a guest, your comment is unpublished.
    JD99 · 5 years ago
    Hi Guys,
    I would like to exclude zeros, O's and X's from the randomly generated string. How is best to achieve this? Many thanks
    • To post as a guest, your comment is unpublished.
      HelloKelly · 5 years ago
      Hi,
      It is impossible to exclude some characters from the random string when they are generating with the methods in this article.

      However, you can replace the excluded characters with other letters or numbers in the random strings.

      First of all select these strings, and then open the Find and Replace dialog box with hotkeys of Ctrl + F, at last replace each excluded character with a specified letter or number.
  • To post as a guest, your comment is unpublished.
    HappyE · 6 years ago
    Thanks for your sharing. For generate random alphanumeric function is not working in Excel 2003 version. Also, how to make it to 8 digits instead of 4?
    • To post as a guest, your comment is unpublished.
      HelloKelly · 5 years ago
      Hi HappyE,
      To generate 8 digits of random alphanumeric, you can repeat the & CHAR(INT(RAND() * 25 + 65)) 8 times in the function. That’s meaning the function changes to:

      =CHAR(INT(RAND( )* 25 + 65)) & CHAR(INT(RAND() * 25 + 65)) & CHAR(INT(RAND() * 25 + 65)) & CHAR(INT(RAND() * 25 + 65))& CHAR(INT(RAND() * 25 + 65)) & CHAR(INT(RAND() * 25 + 65)) & CHAR(INT(RAND() * 25 + 65))& CHAR(INT(RAND() * 25 + 65))
      • To post as a guest, your comment is unpublished.
        MayZ · 5 years ago
        HelloKelly,
        Great formula, thanks for posting it as I've found it very useful generating passwords. Do you know how to generate it without repeating characters at all? Example - it just returned "JUUJFENQ" but I can't have the repeating 'UU'.
        TIA,
        MayZ
  • To post as a guest, your comment is unpublished.
    HelloKelly · 6 years ago
    Hi LAurent,

    Thank you for your comment. I am very sorry the first method with formula (=CHAR) works well in Excel 2007 and 2010, but not Excel 2003. In Excel 2003, you can try the formula

    =CHAR(INT(RAND()* 25 + 65))
    or
    =CHAR(INT(RAND()* 25 + 65)) & CHAR(INT(RAND()* 25 + 65)) & CHAR(INT(RAND()* 25 + 65)) & CHAR(INT(RAND()* 25 + 65))

    By the way, the VBA code works fine in Excel 2003, and we recommend you try it.
  • To post as a guest, your comment is unpublished.
    LAurent · 6 years ago
    Great tuto
    but The function does not work for me using EXCEL 2003