Remove Invalid Phone Numbers

[indyadmin1974]

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.

 

[anilsolipuram]

Backup your original workbook before trying this macro.

Before executing the macro select the cells for which you want to check
the invalid phone numbers.
1)Macro checks each cell and retrieve only numeric content of each
cell.
2)Removes country code "1" .
3)Checks the length of the numeric value.
4)If the length of the numeric cell value is <10, then the cell value
is made empty.


Sub macro()
Dim r As Range
Dim c As Range
Set r = Selection
For Each c In r
If Not IsNumeric(c.Value) Then
c.Value = get_numeric(c.Value)
Else
c.Value = get_numeric(c.Value)
End If
If (InStr(1, c.Value, "1") = 1) Then
c.Value = Mid(c.Value, 2)
End If
If (Len(c.Value) <> 10) Then
c.Value = ""
End If
Next
End Sub


Function get_numeric(t As Variant)
Dim ph As Variant
For i = 1 To Len(t)
If (IsNumeric(Mid(t, i, 1))) Then
ph = ph & Mid(t, i, 1)
End If
Next
get_numeric = ph
End Function

 

[tkstock]

4. Remove all entries that are 'fake' phone numbers (i.e. 0000000000
or 3171234567)

The only way this would work is if you can check it against a known
"non-fake" list. Why couldn't (317) 123-4567 be a valid phone number?
If you come up with a list of the criteria that makes a phone number
"invalid", then you are merely coming up with a list of if-then
statements to check the number. Other than that, your asking to filter
on a vague notion of validity.

 

[Guest]

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

 

[Guest]

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