7 Ways to Create Random 5 Digit Number Generator in Excel

In Excel, we can generate random numbers in various ways. Depending on our needs, we can generate numbers using a variety of Excel functions, formulas, and features. But distinct functions generate numbers in various ways. So, we’ll show you 7 diverse ways of making a generator that generates a random 5-digit number in Excel in this article.


📁 Download Excel File

Download the Excel file used for the demonstration from the link below.


Learn to Create Random 5 Digit Number Generator in Excel with These 7 Methods

This article will show seven ways of creating a random 5 digit number generator in Microsoft Excel. We want to create a unique ID containing 5 digit number. So, now let us dig into the article to find out how all these methods work.

Approach 1

1. Using RANDBETWEEN Function

This method will use RANDBETWEEN function for generating random 5 digit numbers in Excel. The RANDBETWEEN function returns a random integer between specified digits. It returns new random integers every time a calculation is performed in the worksheet.

Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want the desired result. Here, we are selecting cell B6.
  • Next, insert the formula.

=RANDBETWEEN(10000,99999)

Applying RANDBETWEEN function to generate random 5-digit numbers in Excel.

Here, this function will return any integer between 10000 and 99999 since we want 5 digit numbers.

  • After that, drag the File Handle down to get the desired result.

dragging down fill handle after applying randbetween

  • This function will return a new random number each time the sheet calculates. So, if you wish a fixed 5-digit unique number, copy the cells by pressing Ctrl+C and paste them by selecting Paste Values from the paste options in the ribbon under Home tab.

copying and pasting

  • Finally, you will have the random 5digit number.

list of unique id dataset

📕 Read More: Generating Random Number Between Two Numbers in Excel

Approach 2

2. Employing LEFT & RANDBETWEEN Functions

This method will employ LEFT with RANDBETWEEN to forge random 5 digit numbers in Excel.  The LEFT function extracts from a text string according to the specified characters. On the other hand, the RANDBETWEEN function returns a random integer between specified digits. It returns new random integers every time a calculation is performed in the worksheet.

So, let us see the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell B6 to insert the formula.

=LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),$D$6)

Applying LEFT and RANDBETWEEN functions to generate random 5-digit numbers in Excel.

🔨 Formula Breakdown

Here, the formula we inserted in cell B6 is:

LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),$D$6)

👉 In this function, the RANDBETWEEN function generates random number of length 31.

👉 Then, the LEFT function returns left most 5 characters.

  • Lastly, drag the File Handle down to get the desired result.

dragging fill handle down

📕 Read More: 5 Ways to Generate Random Number Between 0 and 1 in Excel

Approach 3

3. Applying ROUND & RAND Functions

Now, in this method, we will apply ROUND with RAND function to create random 5 digit numbers in Excel.  The ROUND function returns the specified number of digits after rounding. On the other hand, the RAND function returns a random integer between specified digits. It returns new random integers every time a calculation is performed in the worksheet.

So, let us see the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell B6 and insert the formula.

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

Applying ROUND & RAND functions to generate random 5-digit numbers in Excel.

🔨 Formula Breakdown

Here, the formula we inserted in cell B6 is:

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

👉 In this function, RAND generates random number of 5-digits.

👉 Then, ROUND rounds the number up to 0 decimals, converting it into an integer.

  • Lastly, drag the File Handle down to get the desired result.

dragging fill handle down

📕 Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel

Approach 4

4. Using INT and RAND Functions

In this method, we will apply INT, and RAND functions to make a random 5 digit numbers in Excel.  The INT function round the specified number to the nearest integer. On the other hand, the RAND function returns a random integer between specified digits. It returns new random integers every time a calculation is performed in the worksheet.

Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell B6 and insert the formula.

=INT(RAND()*(99999-10000)+10000)

Applying INT &RAND functions to generate random 5-digit numbers in Excel.

🔨 Formula Breakdown

Here, the formula we inserted in cell B6 is:

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

👉 In this function, RAND generates random number of 5-digits.

👉 Then, the INT function helps to round the generated number to the closest integer.

  • Lastly, drag the File Handle down to get the desired result.

dragging fill handle down

📕 Read More: 6 Ways to Generate Random 10-Digit Number in Excel

Approach 5

5. Utilizing RANDARRAY Function

Now, in this method, we will apply RANDARRAY functions to spawn random 5 digit numbers in Excel. RANDARRAY function generates random number array. We can specify the minimum and maximum values, number of columns and rows to fill, and whether or not to return a whole number or a decimal value.

Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want the desired result. Here, we are selecting cell B6.
  • Next, insert the formula.

=RANDARRAY(5,2,10000,99999,TRUE)

Applying RANDARRAY function to generate random 5-digit numbers in Excel.

Here, 5 is the row number, 2 is the column number, 10000 is the minimum number to start and 99999 is the max number to end and TRUE is to return an integer value.

📕 Read More: 4 Ways to Introduce Random Number in Excel with Decimals

Approach 6

6. Applying Analysis Toolpak Feature

This method will apply the Analysis Toolpak feature to generate a random 5 digit number in Excel. Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, click on the File menu and select Options.
  • Now Excel Options named box will appear. Next, select Add-ins. Then, under Manage option, select Excel Add-ins and click on Go…

selecting add-ins from options

  • Then, another box named Add-ins will appear. Click on the Analysis Toolpak option and then press OK.

enabling data analysis toolpak

  • Then, select Data Analysis from Data tab.

data analysis toolpak on the ribbon

  • After that, a box named Data Analysis will pop up. Choose Random Number Generation as Analysis Tools and then press OK.

Selecting the Analysis Toolpak feature to generate random 5-digit numbers in Excel

  • Then, a Random Number Generation box will appear. Insert 2 beside Number of Variables, 6 beside Number of Random Numbers, Uniform in the Distribution option, 10000 and 99999 as Parameters, and $B$6 as Output Range.

Random number generating using the Analysis Toolpak feature in Excel.

  • Lastly, the result looks like this.

unique numbers generated from toolpak

📕 Read More: 8 Easy Tricks to Generate Random Data in Excel

Approach 7

7. Embedding VBA

This method will demonstrate how to generate a random 5 digit number in excel using Microsoft Visual Basic Application (VBA).

So, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, navigate to the Developer tab and select Visual Basic from the code group.

visual basic on the ribbon

  • Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

inserting module in vba window

  • Enter the code and save it.
Sub GenerateRandom5DigitNumber()

Dim A As Integer

For A = 6 To 10

    ActiveSheet.Cells(A, 2) = Round(Rnd() * (99999 - 10000) + 10000, 0)

Next A

End Sub
  • Now, press Alt+F8, select GenerateRandom5DigitNumber from Macros, and click on Run.

Embeding a VBA code for generating random 5-digit numbers in Excel

  • Lastly, the result looks like this.

random numbers generated from vba


📄 Important Notes

🖊️  RAND, RANDARRAY, and RANDBETWEEN, the output of these functions change each time the sheet performs any calculations. So, the numbers keep changing. If you do not want the numbers to change, copy and paste them using the paste values option.

🖊️  Using the RANDARRAY function, you can simultaneously generate 5 digit numbers in multiple columns.


📝 Takeaways from This Article

📌  This article demonstrated 7 methods as a random 5 digit number generator within Excel.

📌  In the first method, we showed the use of the RANDBETWEEN function.

📌  Then, we applied LEFT and RANDBETWEEN functions.

📌  Next, we showed the use of the ROUND and RAND functions.

📌  After that, we applied INT and RAND functions.

📌  Next, we showed the use of the RANDARRAY function.

📌  Then, we applied the Analysis Toolpak feature.

📌  Finally, we embedded a VBA code as a random 5 digit number generator within Excel.


Conclusion

This article has demonstrated 7 methods of random 5 digit number generator within Excel. All these methods are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.


Related Article

(Visited 50 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo