Removing Symobls from phone numbers

  • Thread starter Thread starter Mark C via AccessMonster.com
  • Start date Start date
M

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?
 
I have found that this is what it is replacing... if the phone number has xxx-
xxx -1xxx it will remove the -1

Mark said:
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?
Click to expand...
 
Mark said:
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.
Click to expand...


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,"(",""),")",""),"-",""),"
", "")
 
Well that works perfectly... thanks a ton for the help

Marshall said:
I import data daily. when I get phone numbers they can be in any of the
following formats
Click to expand...
[quoted text clipped - 9 lines]
it works mostly but on random phone numbers it takes off a number.
Click to expand...

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,"(",""),")",""),"-",""),"
", "")
Click to expand...
 
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
 
Back
Top