[AccessD] Replace Special Characters

Terri & Group

Function ReturnOneWord(s As String)
    'Mattys Consulting
    'For stripping out unwanted characters
    Dim varChar As Variant, i As Integer, j As Integer, k As Integer,
    Dim P As Integer, LenSep As Integer
    varChar = Array(" ", "-", ".", ",", ":", ";", "(", ")", "\", "/", "'",
Chr(9),  _
                             Chr(10), Chr(13), "&", "%of", "+", "#", "?",
"$", "_")
    For i = 0 To UBound(varChar)
        P = InStr(1, s, varChar(i))
        If P > 0 Then
            j = CountCSVWords(s, varChar(i))
            For k = 1 To j
                P = InStr(1, s, varChar(i))
                If P > 0 Then
                    LenSep = Len(varChar(i)) - 1
                    s = Trim$(Left$(s, P - 1)) & Trim$(Right(s, Len(s) - P -
LenSep))
                End If
            Next
        End If
    Next
    ReturnOneWord = s
End Function

Private Function CountUnwanted(s As String, sep) As Integer
' Counts words in a string separated by unwanted chars
Dim WC As Integer, pos As Integer
  If VarType(s) <> 8 Or Len(s) = 0 Then
    CountUnwanted= 0
    Exit Function
  End If
  WC = 1
  pos = InStr(s, sep)
  Do While pos > 0
    WC = WC + 1
    pos = InStr(pos + 1, s, sep)
  Loop
  CountUnwanted= WC
End Function

Michael R. Mattys
www.mattysconsulting.com

----- Original Message -----
From: "Terri Jarus" <jarus at amerinet-gpo.com>
To: <accessd at databaseadvisors.com>
Sent: Thursday, April 10, 2003 3:39 PM
Subject: [AccessD] Replace Special Characters


> I want to be able to go through a column of data and strip out any
> special characters (,./"'-*&!, etc.) and store the "clean" data to a new
> column.
>
> "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890"
>
> How would I go about doing this?
>
> Thanks for any help.
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com