Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Generating Random Phone Numbers

Posted on November 20th, 2008 in Analytics , Learn Excel - 11 comments

random-phone-number-generator-excelOften when you are writing code or testing an application, it might be helpful to have random phone numbers generated for you.

Here is an excel formula that I use: =value(left(substitute(rand(),"0.",""),10))

try these 2 instead (thanks to John)

=RAND()*(9999999999-1000000000)+1000000000

=RANDBETWEEN(1000000000,9999999999) [needs analysis toolpack add-in or excel 2007]

Even though it works most of the time as excel rand() formula generates long decimal values between 0 and 1, it often returns fewer than 10 digits. You can add zeros at the left side to compensate that though.

Here is a shared Google docs spreadsheet with 200 random phone numbers generated for you. I am not sure how often Google refreshes the formulas on shared workbooks, so please be patient if you see same numbers generated twice.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

11 Responses to “Generating Random Phone Numbers”

  1. This one ensures that you don’t have any 1 or 2-digit area codes:

    =RAND()*(9999999999-1000000000)+1000000000

    If you have Excel 2007 (or Analysis Toolpak installed), this is even simpler:

    =RANDBETWEEN(1000000000,9999999999)

  2. Chandoo says:

    @John: thank you very much for that. I have updated the post with your formulas as they are simpler and better than mine.. :)

  3. Pankaj Verma says:

    Hi Chandoo,

    I have been a regular visitor to your blog and getting benefitted by the info posted by you and others.

    am glad you highlighted the use of random numbers for testing. Being a business analyst (from banking background) and Having used excel to create test data in the similar manner, as you mentioned in your post, I think excel is a wonderful tool for ceating tesing data.
    For example, for an application related to a loan management, my development team needed a sizeable data for testing. We needed following columns:

    Customer Name, Date of Birth, Loan Product, Branch Location, Approved Amount, Disbursement Amount, Loan Tenure, sourcing agent etc..

    In such scenario, excel proved to be a “god sent” for us.

    For customer name I took name from popular Baby Name sites / Indian God Names etc.., used random numbers and vlookup and concatenate funtion to change surnames, initials etc (so that one name can be used multiple times by changing surname / initial etc.)

    For other columns, again randbetween, Vlookup / Hlookup and concactenate functions proved handy. Within an hour, I was able to create a data of 40,000 loan applications having sufficient variety to satisfy the testing requirement. Now creation of functional test cases was a cake walk and believe me that was the first time excel was used in our company for such purposes. But now it is a daily ritual…….isn’t that amazing.

    Regards,

    Pankaj Verma

  4. JP says:

    You could also try the following site. You can create fake SSN, phone numbers, etc, for testing purposes.

    http://www.fakenamegenerator.com/order.php

  5. Chandoo says:

    @Pankaj: This is awesome stuff…

    I have been trying to figure out ways to generate random names (never had the need though, so not really researched the option). You have shared a simple and elegant technique which I am sure many readers (and me) will find easy to use and build up on.

    There are several fun ways in which you can use excel to become totally productive at work and various other things. I use excel a lot to generate blog posts, renaming files (my music collection), process web data and so much more.

    Thank you very much for sharing your tricks with us. I may use this in another post so that many more people will have the privilege of knowing this.

  6. [...] needs analysis toolpak if you are using excel 2003 or earlier… Get Full Tip 31. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” [...]

  7. [...] To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… Get Full Tip [...]

  8. Bob says:

    Hi,

    =ROUND((RAND()*10000000000),0)

    works best.

    Cheers,
    Bob

Leave a Reply