remove unwanted characters from a string.

[90405]

remove unwanted characters from a string.
Could anyone advise how I could remove unwanted characters from a string.


In particular, I have imported telephone names and numbers into EXCEL, but would like to retain or extract only all text or numeric characaters without other symbols, i.e. clean the text up.

as an example:
John & Sons * (pty. ltd)
tel: 02/365,37890

remove from above any symbols or ALL non ASCII characaters as given below:
&, (./, ?^$£à§é!|"%/
- or any non text or numeric charcater


as I do not have much familiarity with programming something done if possible in Access and simple would be appreciated.

thanking you in advance

 

[FloBob]

here is a function that will remove all but alpha characters from a string. I will leave it to you to build a function to remove every character accept numeric ones from your string (for your phone numbers). What I did was convert the string to the ASCII numeric values of each character and looped thru to find which ones were letters and which ones were not. You call this function by using removeall("Whatever String You like !!!!!") which will come out as "Whatever String You like". I hope this helps.

Public Function removeall(stringData As String) As String
Dim letter As Integer 
Dim final As String


For I = 1 To Len(stringData) 'loop thru each char in stringData
    
    letter = asc(Mid(stringData, I, 1)) 'find the char and assign asc value
    
    Select Case letter 'Determine what type of char it is
        Case Is < 91 And letter > 64 'is an upper case char
            final = final & Chr(letter)
        Case Is < 123 And letter > 96 'is an lower case char
            final = final & Chr(letter)
        Case Is = 32  'is a space
            final = final & Chr(letter)
    End Select

Next I
removeall = final

End Function

 

[rgvitali]

remove unwanted characters from a string

FloBob,

I found this discussion thread and tried to apply it to my situation. I sort of understand the function ASC being used, but I'm not sure how to call the string I want.
I'm looking to parse out numeric values in a field and alpha into two new fields on a table.
That is: Field1 is exiting field containing something like "AB123XYZ"
I was NewField1 to be "123" and NewField2 to be "ABXYZ".

How should I set my table field as my source?

Thanks,