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.

Random 5 Digit Number Generator in Excel: 7 Examples

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.

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

• Consequently, we will get the below output.

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

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

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

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.

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

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:

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.

• As a consequence, you 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.

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.

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

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.

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

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.

• Secondly, select Options.

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

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

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

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

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

• 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).

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.

• 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

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

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.

Download Practice Workbook

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

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.

<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel