Removing Symobls from phone numbers

[Mark C via AccessMonster.com]

I import data daily. when I get phone numbers they can be in any of the
following formats

9999999999
(999) 999-9999
999-999-9999
(999)999-9999

I have created an update query that does the following

Uptate to: replace([homephone],"(" and ")" and "-" and " ", "")

it works mostly but on random phone numbers it takes off a number.

Any ideas?

 

[Mark C via AccessMonster.com]

I have found that this is what it is replacing... if the phone number has xxx-
xxx -1xxx it will remove the -1

I import data daily. when I get phone numbers they can be in any of the
following formats

9999999999
(999) 999-9999
999-999-9999
(999)999-9999

I have created an update query that does the following

Uptate to: replace([homephone],"(" and ")" and "-" and " ", "")

it works mostly but on random phone numbers it takes off a number.

Any ideas?

 

[Marshall Barton]

I import data daily. when I get phone numbers they can be in any of the
following formats

9999999999
(999) 999-9999
999-999-9999
(999)999-9999

I have created an update query that does the following

Uptate to: replace([homephone],"(" and ")" and "-" and " ", "")

it works mostly but on random phone numbers it takes off a number.

I don't see how that syntax could work for much of anything.
I would expect to this to work correctly:

Replace(Replace(Replace(Replace(homephone,"(",""),")",""),"-",""),"
", "")

 

[Mark C via AccessMonster.com]

Well that works perfectly... thanks a ton for the help

I import data daily. when I get phone numbers they can be in any of the
following formats

[quoted text clipped - 9 lines]

it works mostly but on random phone numbers it takes off a number.

I don't see how that syntax could work for much of anything.
I would expect to this to work correctly:

Replace(Replace(Replace(Replace(homephone,"(",""),")",""),"-",""),"
", "")

 

[Guest]

I like to use this function to cleanup imported data. This is a generic
function but you can easly change it to work on phone numbers.

Function stripme(mystring)
Dim aaa As String
Dim bbb As Integer
Dim ccc As String
Dim ddd As String
Dim eee As String

ddd = Space(1)
eee = Space(1)

If IsNull(mystring) Then
mystring = Space(1)
End If

Rem I have to strip out all charaters that are not numberic and alpha.
aaa = "1234567890abcdefghijklmnopqrstuvwxyz" <== change to "1234567890"
For bbb = 1 To Len(mystring)
ccc = Mid(mystring, bbb, 1)
ddd = InStr(1, aaa, ccc)
If ddd > 0 Then
eee = eee & ccc
End If
Next bbb
stripme = Trim(eee) <== format the phone number any way you like.
End Function