Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

Generate random number with the given specific length in Excel

Author Sun Last modified

In modern office tasks, you might need to generate random numbers or passwords with a certain number of digits, such as for temporary accounts, codes, user registrations, or system testing. Manually creating such random numbers can be tedious and error-prone. The good news is that Excel provides several flexible methods using formulas, tools, and VBA to efficiently generate random numbers of a specified length. This tutorial will walk you through practical approaches—including Excel formulas, and the built-in Kutools for Excel tool—to help you generate random numbers of desired lengths according to your actual needs and different application scenarios.

Random number in a given specific length using a formula

Random number with leading zeros using TEXT and RANDBETWEEN

Generate multiple random numbers at once using array formulas / SEQUENCE / RANDARRAY (Microsoft 365/2021)

Random number or other characters of a given length using a handy tool

Sample file


Random number in a given specific length using a formula

Random number in a fixed length

Suppose you wish to generate a random number string with a fixed length, such as 6 digits (from 100000 to 999999). You can use the following formula, which is especially suitable when you do not need leading zeros:

=RANDBETWEEN(100000,999999)

Enter this formula in a cell (for example, B1), then press Enter. Excel will generate a 6-digit random number. If you recalculate, the value updates each time. This approach ensures that your random number is always 6 digits long but does not preserve leading zeros. If you require a different length, such as 4 or 8 digits, simply change the two numbers to the desired number of digits (e.g.,1000 and 9999 for 4 digits).

Note: Whenever you update, recalculate, or reopen the worksheet, the random number may change as it is dynamically generated each time. To make the number static, you can copy the cell and use Paste Values to overwrite it.
insert random numbers in a given specific length by using formula

Random number in a changed length based on your input

In some scenarios, you may want the random number’s length to change dynamically according to the number you type in another cell (for example, you specify "5" in cell A1 and a 5-digit number appears in A2). You can achieve this with the formula below:

Select cell A2 and type this formula:

=LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999), A1)

Press Enter. Initially, if A1 is empty, the formula will return a blank. Now whenever you type a number (like5 or9) in cell A1 and confirm, a random number of that specific length will appear in cell A2.
Random number in a changed length

Please note that, since the length is customizable, very large values in A1 may cause the formula to run out of random digits, or not yield a truly random result. In most password applications,4-16 digits is practical.

Note: This formula’s output is updated every time the worksheet recalculates, so if you need the value to remain unchanged, use Paste Values after generating.

Random number with leading zeros using TEXT and RANDBETWEEN

If your application requires random numbers with a fixed length, including leading zeros (for example, to generate codes like 001542 instead of 1542), you can combine the TEXT and RANDBETWEEN functions. This approach is suitable for generating random numbers in scenarios where the number format should always have a consistent number of digits, regardless of any leading zeros.

1. In your target cell (for example, B1), enter the following formula to generate a 6-digit number with leading zeros:

=TEXT(RANDBETWEEN(0,999999),"000000")

2. Press Enter. The cell displays a random number as text with exactly 6 digits, including leading zeros when needed (e.g.,000673).

You can adjust the zeros in the format string (e.g., "0000" for 4 digits, "00000000" for 8 digits). To generate multiple codes, drag down the formula to copy it to more cells.

Tip: Although the result looks like a number, it is formatted as text. If you need these codes as numbers without leading zeros, use the standard RANDBETWEEN formula. If you need the codes to remain static, remember to copy and Paste Values.

Scenario analysis: This solution is ideal when your application, such as web account registration, requires codes with a specific length and preserves formatting with leading zeros. It avoids the common Excel behavior where leading zeros are dropped in purely numeric formats.

Generate multiple random numbers at once using array formulas / SEQUENCE / RANDARRAY (Microsoft 365/2021)

If you need to generate a series or batch of random numbers with the same digit length, Microsoft 365/2021 provides advanced functions that allow you to generate multiple values in a single formula—useful for quickly creating a whole column of random codes for imports, tests, or inventory numbers.

Select your top-left cell (e.g., E1) and use this formula to create 10 values at once:

=TEXT(RANDBETWEEN(SEQUENCE(10,1,0,0),99999999),"00000000")

Alternatively, for greater efficiency in Microsoft 365/2021:

=TEXT(RANDARRAY(10,1,0,99999999,TRUE),"00000000")

This formula instantly fills a column with 10 unique random 8-digit numbers, maintaining leading zeros. You can adjust the row/column arguments to fit your batch needs.

Precautions: Each recalculation will generate new values. Use Paste Values to keep the results fixed. Also, this method requires Microsoft 365/2021 or later that supports dynamic array functions.

Scenario analysis: Convenient for bulk generation of codes, test keys, access pins, and cases where you need a large set of random numbers at once.


Random number or other characters in a given length with using a handy tool

If you not only want random number strings but also need random decimals, integers within a given range, or even random text strings, dates, times, or custom lists, try the Insert Random Data function in Kutools for Excel. This utility allows you to quickly generate a variety of random data, with optional length and value range, for various office automation needs.

After free installing Kutools for Excel, follow these steps:

Select the range of cells where you want random data. Go to Kutools > Insert > Insert Random Data.
Insert Random Data with kutools

In the Insert Random Data dialog, select your desired data type—number, decimal, date, time, string, or custom list. Specify settings such as minimum/maximum value, digit length, or character set as needed.

Insert random integers or decimals
Insert random integers Insert random decimals

Insert random date or time
Insert random date Insert random time

Insert random string or custom list
Insert random string Insert random custom list

You can batch-generate static data—once inserted, random values do not change unless you run the tool again. This approach is particularly helpful for test data, coding, bulk registrations, and more.

Advantages: No formulas or code required; highly flexible and user friendly. Precaution: Kutools is an extra add-in and must be downloaded and installed separately.


Sample file

Click to download sample file


Other Operations (Articles)

Generate random number by given certain mean and standard deviation in Excel
In some certain cases, you may want to generate a series of random numbers based on a given specific mean and standard deviation, how could you quickly solve this problem? Now this tutorial will introduce a way for you to handle it in Excel.

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 only random even or odd numbers in Excel
In this tutorial, I will introduce some formulas to help you generate only random even numbers or only random odd numbers in Excel.

Generate random date between two dates quickly in Excel
When you use an Excel file, sometimes you want to generate random dates for some purpose. Of course, you can enter the date manually one by one, but if you need to insert multiple dates, this method will be time-consuming and boring. How to quickly generate a random date on Earth in Excel?


  • 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 and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... 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...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • 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...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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