Random 5 Digit Number Generator in Excel (7 Examples)

While working in Microsoft Excel, sometimes we require a random 5 digit number generator. Especially while doing statistical analysis we might need to generate 5-digit numbers. Again we may use a 5-digit number generator to create passwords or IDs. Luckily excel has several options to get random 5-digit numbers. This article will guide you to use those options.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


7 Examples of Random 5 Digit Number Generator in Excel

1. Excel RANDBETWEEN Function as 5 Digit Number Generator

First of all, we will use the RANDBETWEEN function as a 5 digit number generator. This function allows us to get random numbers between specified numbers. For example, I will generate 5 digit numbers between 10000 and 99999. To get the desired result follow the below steps.

Steps:

  • Type the below formula in Cell B5 and press Enter.
=RANDBETWEEN(10000,99999)

Excel RANDBETWEEN Function as 5 Digit Number Generator

  • As a result, we will get the below 5-digit number. Next, use the Fill Handle (+) tool to get 5-digit numbers over the range B6:B10.

Excel RANDBETWEEN Function as 5 Digit Number Generator

  • Consequently, we will get the below output.

Excel RANDBETWEEN Function as 5 Digit Number Generator

Note:

The RANDBETWEEN function is a volatile function. Random numbers generated by this function change every time a cell on the worksheet is calculated. If you want to avoid these changes in numbers follow the below steps.
Steps:

  • First copy the random numbers generated by the RANDBETWEEN formula by following Home > Copy or Ctrl + C.

Excel RANDBETWEEN Function as 5 Digit Number Generator

  • Then paste them as Values by following Home > Paste > Paste Values (see screenshot).

Excel RANDBETWEEN Function as 5 Digit Number Generator

  • As a result, you will get the numbers as values.

Read More: Excel Formula to Generate Random Number (5 examples)


2. Generate Random 5 Digit Number with LEFT & RANDBETWEEN Functions

In this method, I will use a formula with the combination of LEFT and RANDBETWEEN functions. This formula will generate random numbers depending on the length of the numbers given in the cell referenced by the formula. Let’s see how we can do the task.

Steps:

  • First, type the below formula in Cell B6 and hit Enter. The formula will return an empty cell.
=LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),B5)

Generate Random 5 Digit Number with LEFT & RANDBETWEEN Functions

  • Now, type 5 in Cell B5 as you need a random number with 5-digits. Once you hit Enter, in Cell B6 you will get a 5-digit random number.

🔎 How Does the Formula Work?

  • RANDBETWEEN(1,9)

Here the above formula returns a random number between 1 to 9.

  • RANDBETWEEN(0,999999999999999)

Here the RANDBETWEEN function returns a random number between 0 to 999999999999999.

  • LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),B5

Lastly, the above formula returns a random number containing the length of Cell B5.

Read More: Random 4 Digit Number Generator in Excel (8 Examples)


3. Create 5 Digit Number Using ROUND & RAND Functions in Excel

This time I will use the combination of  ROUND and RAND functions as a 5-digit random number generator. The generic formula for generating the numbers is:

=ROUND(RAND()*(Y-X)+X,0)

Where X and Y is the bottom and top number between which you want to generate 5-digit numbers.

Steps:

  • Type the below formula in Cell B5. Next press Enter.
=ROUND(RAND()*(99999-10000)+10000,0)

Create 5 Digit Number Using ROUND & RAND Functions in Excel

  • As a consequence, yow will the below 5-digit numbers.

🔎 How Does the Formula Work?

  • RAND()

Here the RAND function generates random decimal numbers.

  • RAND()*(99999-10000)+10000

In this part, the result of the RAND function is multiplied by 89999. Then the result is added to 1000.

  • ROUND(RAND()*(99999-10000)+10000,0)

Finally, the ROUND function rounds the result of the previous formula to zero decimal places.

Read More: Generate Random Number in Excel with Decimals (3 Methods)


4. Combine INT & RAND Functions as 5 Digit Number Generator

This method is kind of similar to the previous method. Instead of the ROUND function, we will use the INT function here. To create 5-digit random numbers between 10000 and 99999 follow the below steps.

Steps:

  • Type the below formula in Cell B5. Then press Enter.
=INT(RAND()*(99999-10000)+10000)

Combine INT & RAND Functions as 5 Digit Number Generator

  • As a result, you will get the following output.

Here the above formula works in a similar way mentioned in Method 3. First, the RAND function generates random decimal numbers. Then the resulting decimal number is multiplied by 89999 and added to 1000. Lastly, the INT function rounds the number to the nearest 5-digit integer.

Read More: How to Generate Random 10 Digit Number in Excel (6 Methods)


Similar Readings


5. Create Random 5 Digit Number with RANDARRAY Function

You can use the RANDARRY function as a random 5-digit number generator. To create 5-digit random integers between 10000 and 99999, and spread over 2 columns and 6 rows follow the below instructions.

Steps:

  • Type the following formula in Cell B5.
=RANDARRAY(6,2,10000,99999,TRUE)

Create Random 5 Digit Number with RANDARRAY Function

  • Once you hit Enter, the above formula returns 5-digit random numbers (integers) over columns B & C and rows 5:10.

Read More: How to Generate Random Numbers Without Duplicates in Excel (7 Ways)


6. Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

In this method, I will use an Excel add-in as a 5-digit number generator. First I will show you adding the add-in to the Excel Ribbon. Later, I will use that add-in to generate 5-digit random numbers.

Steps:

  • First, go to the File tab from the ribbon.

Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

  • Secondly, select Options.

Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

  • Next, the Excel Options dialog will appear, click on Add-ins. Check Excel Add-ins selected from the Manage drop-down menu and press Go.

Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

  • As a result, the Add-ins dialog will show up, put a checkmark on Analysis ToolPak and press OK.

Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

  • Now go to the Data tab, and the Data Analysis option is available. Click on it.

Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

  • Consequently, the Data Analysis dialog box appears, select Random Number Generation from the Analysis Tools list, and press OK.

Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

  • When the Random Number Generation dialog shows up, enter 2 as the Number of Variables, and 6 as the Number of Random Numbers.
  • Then, choose Uniform from the Distribution drop-down. In the Parameters section enter the range of 5 digit numbers (10000 and 99999) in the Between field.
  • After that, select the Output Range and choose the destination cell (here Cell $B$5). Press OK to close the dialog.

Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

  • Finally, we can see the output below.

Note:

  • 5-digit random numbers generated by Analysis ToolPak contain decimals. To convert those numbers to zero decimal places you can use the ROUND or INT functions (described in Method 4 and Method 5).

Read More: Random Number Generator with Data Analysis Tool and Functions in Excel


7. Apply Excel VBA as 5 Digit Number Generator

You can use Excel VBA to generate 5-digit random numbers.

Steps:

  • First, go to the sheet where you want to get the 5-digit random numbers. Then right-click on the sheet name and select View Code to bring up the VBA window.

Apply Excel VBA as 5 Digit Number Generator

  • Now type the below code in the Module and run using the F5 key.
Sub Random5DigitNumber()
Dim N As Integer
For N = 5 To 10
    ActiveSheet.Cells(N, 2) = Round(Rnd() * (99999 - 10000) + 10000, 0)
Next N
End Sub

Apply Excel VBA as 5 Digit Number Generator

  • Finally, upon running the code you will get the below 5-digit numbers.

Read More: How to Generate Random Number in a Range with Excel VBA


Things to Remember

  • The result we receive from the RANDBETWEEN function contains duplicates. To detect the duplicate numbers you can use the RANK.EQ function in excel.
  • The RAND function is also a volatile function. You can convert the results returned by the RAND formula to values using the Paste Special option.

Conclusion

In the above article, I have tried to discuss several examples of a random 5 digit number generator in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo