UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Help Eliminating Invisible Characters, Access 2016    
 
   
ChipC
post Nov 30 2017, 02:16 PM
Post#1



Posts: 24
Joined: 7-November 07



Our database includes a lot of names cut and pasted from places on the web. As a result we've picked up a lot of weird invisible characters which cause trouble if I export the data and try to process it.

I can't seem to paste the character I found, so here's some information about it:
Decimal NCRs: & # 8 2 0 7 ; (I had to add spaces between the characters so the forum wouldn't eat the input; wasn't sure how to escape it.)
HTML/XML / Hex NCRs: ‏
UTF-8 code units: E2 80 8F
UTF-16 code units: 200F

I've tried to replace these when I export the data with something along the lines of...
myText = Replace(myText, Chr(8207), "")
myText = Replace(myText, Chr(200F), "")
...But none of these work.

Also, I'm reasonably sure there's a whole range of characters doing this, but I don't know how to start tracking them all.

Any help is appreciated. Thank you.
Go to the top of the page
 
DanielPineault
post Nov 30 2017, 02:40 PM
Post#2


UtterAccess VIP
Posts: 6,277
Joined: 30-June 11



You might be able to use, or modify to suit your needs, this code I picked up many years ago
CODE
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


CODE
myText = StripSpChars(myText)
Go to the top of the page
 
ChipC
post Nov 30 2017, 03:34 PM
Post#3



Posts: 24
Joined: 7-November 07



Daniel, that's a good thought. I tried it, modified to just eliminate anything outside the normal range:

CODE
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 <= 255 Then
            StripSpChars = StripSpChars & Chr(intChar)
        End If
    Next lngCtr
End Function


Instead of eliminating the characters outright, it replaced them with question marks. I had previously run something to get the ASCII value of the character at the far right of a string that ends with the character, and I'd got the 63 value which means question mark. So I guess this character is compound somehow.

Also, running my text through this function changed some special characters; "Société française" becomes "Socie´te´ franc¸aise".

I think I'm closer but I'm not there yet.
Go to the top of the page
 
ChipC
post Nov 30 2017, 04:13 PM
Post#4



Posts: 24
Joined: 7-November 07



This function has helped me find a few things:

Whether I come at this character from "Asc(Right([a],1))" or "Asc(Left([a],1))", I get a value of 63 for this character. Access's "Asc" believes it's a question mark, which it clearly isn't.

So for that reason, my modified StripSpChars only modified my text by running it all through "Chr", which also had effects I don't want.

Is there an alternative to "Asc"/"Chr"?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th December 2018 - 08:37 AM