Often 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.
13 Responses to “Generating Random Phone Numbers”
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)
@John: thank you very much for that. I have updated the post with your formulas as they are simpler and better than mine.. 🙂
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
You could also try the following site. You can create fake SSN, phone numbers, etc, for testing purposes.
http://www.fakenamegenerator.com/order.php
@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.
Chandoo could you plz let us now to rename collection of music files at a stretch using excel...........????
[...] 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),” [...]
[...] Random phone number | Shuffle a list | More on Random Formulas [...]
[...] To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… Get Full Tip [...]
Hi,
=ROUND((RAND()*10000000000),0)
works best.
Cheers,
Bob
You can use these softwares to generate random phone numbers
http://lantechsoft.com/mobile-number.html
http://www.theskysoft.com/random-number-generator.html
Hi Chandu,
Can you help me with generating 100 set of random numbers between 1 to 7 in seven coulumns without any duplicates in any row. do we have any formula, this can be done through macros, but i was looking for any formula or anything.
awaiting for any solution.
Thanks
Avinash
It is very informative, helpful and time less consuming. you can get random number with no time by using this formulae.