how to remove specific characters from a text field

[Guest]

I have phone numbers stored in a field as plain text. I cannot control how
the data is entered, I only recieve a csv data dump. The problem is that
people enter the phone number different ways. Such as (111) 123-4567,
111-123-4567, (111)123-4567 (no space after area code), 1111234567 etc. Is
their a way to remove the extra spaces and characters and leave just the
numbers, so I can format it on output or display, instead of just leaving it
display as a text field as they entered it?

Thanks for your help,
Superd707

 

[Guest]

Here are 2 functions that should do the trick. in your particular case the
2nd one is probably your ticket.

Public Function StripSpChars(strString As String) As String
'Remove spaces and special characters from a string
'Source: Dave Hargis, Microsoft Access MVP (klatuu)
Dim lngCtr As Long
Dim intChar As Integer

For lngCtr = 1 To Len(strString)
intChar = Asc(Mid(strString, lngCtr, 1))
If intChar >= 48 And intChar <= 57 Or _
intChar >= 97 And intChar <= 122 Or _
intChar >= 65 And intChar <= 90 Then
StripSpChars = StripSpChars & Chr(intChar)
End If
Next lngCtr
End Function

Public Function StripAllChars(strString As String) As String
'Return only numeric values from a string
'Source: CARDA Consultants Inc. -- www.cardaconsultants.com
Dim lngCtr As Long
Dim intChar As Integer

For lngCtr = 1 To Len(strString)
intChar = Asc(Mid(strString, lngCtr, 1))
If intChar >= 48 And intChar <= 57 Then
StripAllChars = StripAllChars & Chr(intChar)
End If
Next lngCtr
End Function

 

[Guest]

Thank you so much for the help and blazingly fast response!!!

These forums seem to have some really good people and info!

Thanks again.

 

[fredg]

I have phone numbers stored in a field as plain text. I cannot control how
the data is entered, I only recieve a csv data dump. The problem is that
people enter the phone number different ways. Such as (111) 123-4567,
111-123-4567, (111)123-4567 (no space after area code), 1111234567 etc. Is
their a way to remove the extra spaces and characters and leave just the
numbers, so I can format it on output or display, instead of just leaving it
display as a text field as they entered it?

Thanks for your help,
Superd707

Run an Update query to remove spaces - ( and ) to remove those
characters from existing records,

Update YourTable Set YourTable.PhoneField =
Replace(Replace(Replace(Replace([Phonefield],"(",""),")",""),"-",""),"
","")

Then use the same nested replace functions when importing new data.