Excel 2007 : 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!

Originally Posted by NBVC

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!

Originally Posted by NBVC

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!!

Originally Posted by NBVC

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!