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: 22
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: 5,450
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)

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
ChipC
post Nov 30 2017, 03:34 PM
Post#3



Posts: 22
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: 22
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    12th December 2017 - 07:11 AM