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

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=379478

indyadmin1974 Wrote:

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.


--
tkstock


------------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...o&userid=14443
View this thread: http://www.excelforum.com/showthread...hreadid=379478

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