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

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, we will explain different methods to generate a random 10 digit number in Excel. While working on a project in Microsoft Excel sometimes we do not have any particular dataset. So, we have to create a sample one. While creating a sample dataset we may need to generate random numbers. Generally, we have to generate random numbers for a few instances. It’s not the kind of feature which we need to use frequently.


How to Generate Random 10 Digit Number in Excel: 6 Methods

Throughout the whole article, we will explain 6 methods to generate a random 10 digit number. We will use different functions, tools, and VBA code.


1. Combine ROUND and RAND Functions to Generate Random 10 Digit Number

First and foremost, we will use the ROUND function and the RAND function to generate a random 10 digits number.

In Microsoft Excel, the RAND function returns a random number between 0 and 1.

The ROUND function in Excel produces a number that has been rounded to a specified number of digits.

The names of five people are shown in the following image from our dataset. We’ll generate phone numbers for them at random, each with ten digits.

Combine ROUND and RAND Functions to Generate Random 10 Digit Number

Let’s see the steps to perform this action.

STEPS:

  • First, select cell C5.
  • Next, type the following formula in that cell:
=ROUND(RAND()*9999999999+1,0)
  • Press Enter.
  • So, the above action returns a random 10 digit number in cell C5.
  • Then, drag the Fill Handle tool from cell C5 to cell C9.
  • Finally, we get results like the following image.

🔎 How Does the Formula Work?

  • RAND()*9999999999+1: This part multiplies the random number generated by 9999999999 and adds 1 to it.
  • ROUND(RAND()*9999999999+1,0): This part rounds the result that we get from the RAND function.


2. Use RANDBETWEEN Function to Create Random 10 Digit Number in Excel

In the second method, we will use the RANDBETWEEN function to generate a random 10 digit number in Excel.

The RANDBETWEEN function in Excel returns an integer number between two specified numbers.

To explain this method we will continue with our previous dataset.

Let’s see the steps to execute this method.

STEPS:

  • Firstly, select cell C5 and insert the following formula in that cell:
=RANDBETWEEN(1000000000,9999999999)
  • Hit Enter.
  • As a result, we get a random 10 digit number in cell C5.
  • Secondly, drag the Fill Handle tool from cell C5 to the end of the dataset.
  • Lastly, we can see the results in the following image.

Use RANDBETWEEN Function to Create Random 10 Digit Number in Excel


3. Generate Random 10 Digit Number Based on Number of Digits You Type in Different Cell

In the third method, we will generate a random 10 digit number using a unique method. For instance, when we type 10 in cell C5, cell D5 will show a random number of 10 digits within it.

Generate Random 10 Digit Number Based on Number of Digits You Type in Different Cell

We will follow the below steps to perform this method.

STEPS:

  • To begin with, insert the following formula in cells (D5:D9):
=LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999), C5)

  • In addition, type the value 10 in cell C5.
  • Tap Enter.
  • Furthermore, we get a random 10 digit number in cell D5.

  • Finally, input the value 10 in cells (C6:C9). As a result, we also get random 10 digit numbers in cells (D6:D9).

🔎 How Does the Formula Work?

  • RANDBETWEEN(0,999999999999999): This part returns a random 10 digit number.
  • LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999), C5): Returns a random number of fixed digits in cell D5 that we type in cell C5.

4. Apply RANDARRAY Function to Generate Random 10 Digit Number

Another method to generate random numbers in a specific region in our dataset is to use the RANDARRAY function. The RANDARRAY function is only available in Microsoft Excel 365 & Microsoft Excel 2021 versions.

The RANDARRAY function provides a list of random numbers ranging from 0 to 1 which is specified by a number of rows and columns.

In the following dataset, we will randomly generate two phone numbers for each person with the RANDARRAY function.

Apply RANDARRAY Function to Generate Random 10 Digit Number

Let’s see the steps to use the RANDARRAY function.

STEPS:

  • In the beginning, select cell C5.
  • Next, type the following formula in that cell:
=RANDARRAY(5,2,1000000000,9999999999,TRUE)

Apply RANDARRAY Function to Generate Random 10 Digit Number

  • After that, tap Enter.
  • Finally, we get random numbers in cells (C5:D9).


5. Generate10 Digit Number with Analysis Toolpak

Another method to generate a random 10 digit number in Excel is to use an Add-in named ‘Analysis Toolpak’. This method doesn’t require any formula.

To illustrate this method we will use the dataset of our first method. Just follow the below steps to perform this action.

STEPS:

  • Firstly, go to the File tab.

Random 10 Digit Number Generating with Analysis Toolpak

  • Secondly, select Options from the menu.

Random 10 Digit Number Generating with Analysis Toolpak

  • A new pop-up window appears named ‘Excel Options’.
  • Thirdly, click on the option Add-ins on the left side of the window.
  • Next, on the right side scroll down to the bottom. Select the option ‘Excel Add-ins’ from the drop-down and click on the Go button.

Random 10 Digit Number Generating with Analysis Toolpak

  • This will open a pop-up window with a list of all accessible Excel add-ins. Click OK after checking the box for ‘Analysis ToolPak’.

Random 10 Digit Number Generating with Analysis Toolpak

  • Then, select the ‘Data Analysis’ option from the Data tab.

Random 10 Digit Number Generating with Analysis Toolpak

  • It opens a new pop-up window named ‘Data Analysis’.
  • Moreover, scroll down the options in the ‘Analysis Tools’ section. Select the ‘Random Number Generation’ option and then click OK.

  • After that, we get one more pop-up window named ‘Random Number Generation’. We will input values for different parameters to generate random 10 digit numbers.
  • The ‘Number of Variables’ field specifies how many columns we want to fill with random data. We have used the value 1.
  • The number of rows is indicated by the ‘Number of Random Numbers’. We have taken the value 5.
  • In the Distribution field, we have chosen the option Uniform.
  • Set the parameters to a range of 1 to 9999999999.
  • Set the ‘Output Range’ to the array’s beginning which is cell C5.
  • Now click on OK.

  • Finally, we can see randomly generated 10 digit numbers in cells (C5:C9).


6. Insert VBA Code to Create 10 Digit Number in Excel

In the last method, we will generate a random 10 digit number with the use of a VBA code. To demonstrate this method we use the following dataset that we have also used earlier.

Insert VBA Code to Create 10 Digit Number in Excel

Follow the below steps to perform this method.

STEPS:

  • To begin with, right-click on the active sheet and select the option ‘View Code’.

  • The above command opens a new blank VBA code window for that worksheet.
  • In addition, insert the following code in the code window:
Sub Random_10_Digit()
Dim GRN As Integer
For GRN = 5 To 9
ActiveSheet.Cells(GRN, 3) = Round((Rnd() * 9999999999# - 1) + 1, 0)
Next GRN
End Sub
  • Furthermore, click on the Run or press the F5 key to run the code.

Insert VBA Code to Create 10 Digit Number in Excel

  • Lastly, we get 10 digit numbers generated at random in cells (C5:C9).


Download Practice Workbook

We can download the practice workbook from here.


Conclusion

In conclusion, we can easily generate a random 10 digit number in Excel by following this method. To achieve the greatest results, download the practice workbook that has been attached to this article and put it to use. If you have any questions or recommendations, please leave a remark in the box below.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo