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.
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)
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.
- 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.
- Finally, you will have the random 5digit number.
๐ Read More: Generating Random Number Between Two Numbers in Excel
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)
๐จ 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.
๐ Read More: 5 Ways to Generate Random Number Between 0 and 1 in Excel
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)
๐จ 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.
๐ Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel
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)
๐จ 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.
๐ Read More: 6 Ways to Generate Random 10-Digit Number in Excel
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)
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
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โฆ
- Then, another box named Add-ins will appear. Click on the Analysis Toolpak option and then press OK.
- Then, select Data Analysis from Dataย tab.
- After that, a box named Data Analysis will pop up. Choose Random Number Generation as Analysis Tools and then press OK.
- 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.
- Lastly, the result looks like this.
๐ Read More: 8 Easy Tricks to Generate Random Data in Excel
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.
- Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.
- 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.
- Lastly, the result looks like this.
๐ 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.