+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : Finding Bad Phone numbers

  1. #1
    Registered User
    Join Date
    11-02-2010
    Location
    O'Fallon, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Finding Bad Phone numbers

    I have a list of phone numbers that I need to scrub against the DNC list. I have corrected many errors I found that the DNC software would reject when importing the excel data to scrub.

    What I've not been able to do is find the phone numbers that have too few numbers(too many aren't an issue for the scrubbing software).

    I need a way to find phone number that have less than 10 characters.

    Please help.

    Thanks!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Finding Bad Phone numbers

    in an adjacent column, you can use a formula like:

    =len(a1)<10 copied down

    TRUE means the cell has less than 10 chars (including any spaces or dashes, etc.).
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-02-2010
    Location
    O'Fallon, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Finding Bad Phone numbers

    Quote Originally Posted by NBVC View Post
    in an adjacent column, you can use a formula like:

    =len(a1)<10 copied down

    TRUE means the cell has less than 10 chars (including any spaces or dashes, etc.).
    Thank you...That did work but I'm having another issue. Is there a way to do this without including spaces, dashes, etc??

    I need to see only those that have less than 10 digits.

    Thanks again!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Finding Bad Phone numbers

    Something like this:

    =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")","")," ",""))<10

    where each nested Substitute function, replaces one character with a null.

    This so far takes care of open and closing parenthesis, spaces and dashes... you can have up to 7 nested substitute() functions in pre XL2007 and lots more in XL2007 and later...

  5. #5
    Registered User
    Join Date
    11-02-2010
    Location
    O'Fallon, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Thumbs up Re: Finding Bad Phone numbers

    Quote Originally Posted by NBVC View Post
    Something like this:

    =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")","")," ",""))<10

    where each nested Substitute function, replaces one character with a null.

    This so far takes care of open and closing parenthesis, spaces and dashes... you can have up to 7 nested substitute() functions in pre XL2007 and lots more in XL2007 and later...
    Whoa...I missed that one. You got me with all the substitutes. Lets try an example if you don't mind:

    A B

    1 (636) 314-2873 I =LEN(??)


    And beyond that, I have an issue because I have formats such as the one above in the phone number field but also some numbers listed as 636-314-2873 or 636-3142873 or 636314-2873 and (636) 3142873.

    I know this has got to make things complicated but I have been at this for two days and basically I'm down to a list of numbers that the majority are as listed in the example above (636) 314-2873 or 636-314-2873 and out of this list of 15 thousand, there have to be some that have less than 10 digits in them because the DNC software will not let me scrub the data.

    Any more information you can provide is greatly appreciated it. Thanks again!!

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Finding Bad Phone numbers

    Could there also be alpha text in the cells?

  7. #7
    Registered User
    Join Date
    11-02-2010
    Location
    O'Fallon, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Finding Bad Phone numbers

    Quote Originally Posted by NBVC View Post
    Could there also be alpha text in the cells?
    I've already been through all the data and removed all alpha text in the cells(including special characters that shouldn't be there). There should be nothing but numbers 0-9 and (, ), -.

    That is all.

    Thanks again!

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Finding Bad Phone numbers

    Then assuming your first string is in A2, this formula should tell you if there is less than 10 digits (excluding brackets, spaces and dashes)....



    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1