Remove Invalid Phone Numbers

I am trying to figure out if I can use a Macro to do a couple of things to a phone number column:

1. Remove all non-numeric characters
2. Remove all entries that are less than 10 digits
3. Remove the country code (1) from the beginning of all numbers and remove any extension numbers from the end of all numbers
4. Remove all entries that are 'fake' phone numbers (i.e. 0000000000 or 3171234567)

This may be too much to do for one macro but if I can get some help with any of those 4 parts I'd appreciate any help on any part of this.

I assume your valid phone numbers are 10-digit strings: 1234567890 (i.e. no
"(", ")" or "-")

A. Get rid of all less than 10 digits :

if len(trim(ph_Number)) < 10 then ph_Number = "" (or delete the row)

B. Removing country codes is more difficult since country codes can be
horrendous (e.g. from US => Belfast Ireland requires: 011+44+28 before the
number, Berlin Germany is 011+49+30) . If you're just talking a "long
distance 1" for US Numbers then:

if len(Trim(ph_Number)) = 11 and mid(trim(ph_Number),1,1) = "1" then
ph_Number = mid(trim(ph_Number),2,10)


C: Removing "non-numeric characters"
Do you have vanity numbers like 1800badboys you're trying to get rid of??

D: Removing the extension depends on how it is indicated if the number is
followed by "EX" or "EXT" or some such, you can strip off everything from "E"
to the end of the number

If it's just "tacked on" then you can strip off everything beyond the
10th digit..

ph_Number = left(trim(ph_Number),10)


If you need more help/detai post a response

BAC

"indyadmin1974" wrote:

>
> I am trying to figure out if I can use a Macro to do a couple of things
> to a phone number column:
>
> 1. Remove all non-numeric characters
> 2. Remove all entries that are less than 10 digits
> 3. Remove the country code (1) from the beginning of all numbers and
> remove any extension numbers from the end of all numbers
> 4. Remove all entries that are 'fake' phone numbers (i.e. 0000000000
> or 3171234567)
>
> This may be too much to do for one macro but if I can get some help
> with any of those 4 parts I'd appreciate any help on any part of this.
>
>
> --
> indyadmin1974
> ------------------------------------------------------------------------
> indyadmin1974's Profile: http://www.excelforum.com/member.php...o&userid=24339
> View this thread: http://www.excelforum.com/showthread...hreadid=379478
>
>

Here is what I came up with. It takes care of everything (including decimals)
except #4 which is like identifying pornograhy. You know it when you see it
but otherwise it is tough to define.

Option Explicit

Public Sub ValidPhoneNumber()
Dim rngCurrent As Range
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngToDelete As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Range("A2", wks.Range("A65535").End(xlUp))
Set rngToDelete = wks.Range("A65535").End(xlUp).Offset(1, 0)
rngToSearch.Replace ".", "", xlPart

For Each rngCurrent In rngToSearch
rngCurrent.Value = Trim(rngCurrent.Value)
If Not IsNumeric(rngCurrent.Value) Then _
rngCurrent.Value = RemoveNonNumeric(rngCurrent.Value)
If Len(rngCurrent.Value) < 10 Then _
Set rngToDelete = Union(rngToDelete, rngCurrent)
If Left(rngCurrent.Value, 1) = 1 Then _
rngCurrent.Value = Mid(rngCurrent.Value, 2, 10)
rngCurrent.NumberFormat = "(###) ###-####"
Next rngCurrent
rngToDelete.Select
rngToDelete.Delete xlShiftUp
End Sub

Private Function RemoveNonNumeric(ByVal PhoneNumber As String) As Long
Dim intCounter As Integer
Dim intLength As Integer
Dim strReturnValue As Long

intCounter = 1
intLength = Len(PhoneNumber)

Do While intCounter <= intLength
If IsNumeric(Mid(PhoneNumber, intCounter, 1)) Then _
strReturnValue = strReturnValue & Mid(PhoneNumber, intCounter, 1)
intCounter = intCounter + 1
Loop
RemoveNonNumeric = CLng(strReturnValue)
End Function

--
HTH...

Jim Thomlinson


"indyadmin1974" wrote:

>
> I am trying to figure out if I can use a Macro to do a couple of things
> to a phone number column:
>
> 1. Remove all non-numeric characters
> 2. Remove all entries that are less than 10 digits
> 3. Remove the country code (1) from the beginning of all numbers and
> remove any extension numbers from the end of all numbers
> 4. Remove all entries that are 'fake' phone numbers (i.e. 0000000000
> or 3171234567)
>
> This may be too much to do for one macro but if I can get some help
> with any of those 4 parts I'd appreciate any help on any part of this.
>
>
> --
> indyadmin1974
> ------------------------------------------------------------------------
> indyadmin1974's Profile: http://www.excelforum.com/member.php...o&userid=24339
> View this thread: http://www.excelforum.com/showthread...hreadid=379478
>
>