Removing extra characters in phone numbers

[Laurie]

I have a database of 100,000 phone numbers in every
imaginable configuration (213) 555-1212, 213-555-1212,
1(213)5551212, 213/ 555-1212, 555-1212, 2135551212 (you
get my point). Some have area codes, some don't, some
even have extentions like "ext. 123". I have enough
location information to figure out area codes, etc, but
how do I strip out all of the extra character to give me
nothing but numbers? Once I get rid of the extra
characters, I can probably create the algorithms to
figure out the rest, but removing the non-numeric data
has me baffled.

 

[Fredg]

Laurie,
This will clear everything except the numbers.

Paste this function into a Module:

Public Function PhoneNum(strIn as String) As String

Dim intX As Integer
Dim intY As Integer
Dim strNewPhone As String
For intX = 1 To Len(strIn)
intY = Asc(Mid(strIn, intX))
If intY >= 48 And intY <= 57 Then
strNewPhone = strNewPhone & Chr(intY)
End If
Next intX

PhoneNum = strNewPhone

End Function
==========

You can call it from a query.
Add a new column to the query.
NewPhonefieldhoneNum([ExistingPhone])

I'm not sure this is going to help though, as with numbers ranging from 7 to
10 digits + possible extensions of 1 to ? digits, it will probably not be
possible to determine whether the first 3 digits are area code or not, and
if not area code are the last n digits part of the number or the extension.
Perhaps you should alter the code and leave the 'ext 123' part to facilitate
whether there is an extension or not.

Have fun.