Seperate us cell phone number from landline number?

Question

Is there a way i can check if a US number is a cell phone or landline? I have found several sites online, but they are paid per number. Can i use code to do the check?

Thanks

Answer 1

This is less of an Excel question and more of a general question about phone numbers. If there's some way (I don't know of one) that you can freely check a phone number against some registry, then we could use some code to do this in Excel - sure. However, I'm not sure how to check whether a line is mobile or land line.

I did find this SO thread, but it looks like the free options are limited. I did find this site just Googling around, but I have no idea how to check if that's accurate or not. There's this site which also charges, but could help you.

Again, if you (or someone) does find a way to check phone numbers, you could probably automate it with VB, but getting to that point is the tricky part.

(Note: the webpages I linked above I just found Googling around).

Answer 2

I have worked on an admittedly crude way to do what you've requested, using the free service phonevalidator.com. I cannot attest to the accuracy of this service, but I tried for several cell numbers I know and several landlines as well and it was accurate for them.

This option uses VBA to go to the website, automatically enter a number, and scrape the phone type off of the results page. I have made the scraping a function (probably could be optimized better, but it works).

In a standard code module, start with this function:

Public Function GetPhoneNumType(phoneNum As String) As String
        Dim ie As Object
        Dim objResult As Object

        'Initialize IE
        Set ie = CreateObject("InternetExplorer.Application")

        'Go to the phone validator
        ie.Navigate "http://www.phonevalidator.com/index.aspx"

        'Wait for the page to load
        Do While ie.ReadyState <> 4
                DoEvents
        Loop

        'Enter the phone number
        ie.document.getElementByID("ContentPlaceHolder1_txtPhone").Value = phoneNum

        'Click the search button
        ie.document.getElementByID("ContentPlaceHolder1_SearchButton").Click

       'Wait until javascript executes and displays the PhoneTypeLabel
       On Error Resume Next
       Do Until Not objResult Is Nothing
                Set objResult = ie.document.getElementByID("ContentPlaceHolder1_PhoneTypeLabel")
       Loop
       On Error GoTo 0

       'Return the phone type result, either LANDLINE or CELL PHONE
        GetPhoneNumType = objResult.innerText
End Function

This takes in a phoneNum as a string and returns the result either as LANDLINE or CELL PHONE (I'm not sure if there are any other return types).

For purposes of this example, let's assume that I entered two phone numbers (these can be with or without dashes, but should have no other symbols and no leading 1, etc.) in cells A1 and A2, I can call this function like so:

Public Sub TestPhoneNum()
        Dim rngNumbers As Range, c As Range

        Set rngNumbers = Sheets("Sheet1").Range("A1:A2")

        For Each c In rngNumbers
                c.Offset(0, 1).Value = GetPhoneNumType(c.Value)
        Next c

        MsgBox "Complete!"
End Sub

This will put the phone number type in the column adjacent (to the right) of each cell that contains a phone number. You can adjust A1:A2 to be whatever range has the phone numbers. Whichever range you enter, this subroutine will always place the phone number type in the right adjacent cell.

I added the message box because this code is slow, and that notified me when the code was done executing.

Hopefully this gets you at least started.