Results 1 to 4 of 4
  1. #1
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398

    Removing special characters

    Is there anyway to remove the special characters within a text string. Here's an example,

    Original: 8800_857_D9306 - EPIC - Enterprise


    Desired: 8800857D9306EPICEnterprise

    The original string is not always in this format. I'm using this to compare two fields to determine the difference. If I'm able to extract out the special characters, it will make my entire process easier.

    Thanks in advance!!

  2. #2
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could run a function against your original string. The function would reject anything that isn't in A-Z,a-z or 0-9

    Here's a function to do that
    Code:
    Function fExtractStr(ByVal strInString As String) As String
    ' From Dev Ashish
    '(Q) How do I extract only characters from a string which has both numeric and alphanumeric characters?
    
    '(A) Use the following function. Note that the If loop can be modified to extract either both Lower and Upper case character or either
    'Lower or Upper case characters or other combinations.
    
    '************ Code Start **********
    
    Dim lngLen As Long, strOut As String
    Dim i As Long, strTmp As String
    
        lngLen = Len(strInString)
        strOut = ""
        For i = 1 To lngLen
            strTmp = Left$(strInString, 1)
            strInString = Right$(strInString, lngLen - i)
            'The next statement will extract Digits (0-9) and BOTH Lower and Upper case chars
            If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _
                (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Or _
                (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then
                'to extract just lower case, use the limit 97 - 122
                'to extract just upper case, use the limit 65 - 90
                strOut = strOut & strTmp
            End If
        Next i
        fExtractStr = strOut
    End Function
    Sample usage:

    ?fExtractStr("12ws$%&^()fGbHTn_p8")
    12wsfGbHTnp8

    Good luck.

  4. #4
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    Thanks for the suggestions. Here's what I came up with:

    Replace(Replace(Replace(Replace(string, "_", ""), " ", ""), "-", ""), ",", "")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. column alias name with special characters?
    By btappan in forum Queries
    Replies: 3
    Last Post: 02-15-2014, 02:22 PM
  2. Search for special characters
    By davej311 in forum Queries
    Replies: 3
    Last Post: 11-20-2013, 02:35 PM
  3. Need to delete special characters
    By tlrutledge in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:10 AM
  4. Special characters in Default Value and Query
    By blacksaibot in forum Queries
    Replies: 1
    Last Post: 03-07-2012, 10:36 AM
  5. Special Characters
    By orgelizer in forum Access
    Replies: 0
    Last Post: 03-20-2007, 08:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums