Trying to replace £ in text file with nothing - result is  ?!!

[peskywinnets]

So I'm wanting to replace all occurences of £ in text file with nothing (e.g. £1.00 becomes 1.00), with this command...

FileContent = Replace(FileContent, "£", "")

but when I open the text file afterwards, all the occurrences of £ have been turned into  symbol/character

e.g. £1.00 becomes Â1.00

...which means when I try to import the text file into Access it errors on that column.

What am I doing wrong (all other 'replace' commands acting on the same file are working fine!)

 

[The_Doc_Man]

What is the prevailing font for this? Try to find that incorrect symbol using the Character Map (All Programs >> Accessories >> System Tools >> Character Map, then set to the font in question) to see what it is in Hexadecimal. Offhand, I don't see an issue, but for snorts & giggles, see if it does better by replacing the indicated sign with a blank string (not an empty one).

Alternatively, cut/paste that symbol into a Notepad file (that you won't keep). Then use the Edit >> Select All and change fonts to something like USASCII or Lucida Console or some other fairly stable text font to see how it translates.

Also, according to an MSDN article, you don't even need the "" that is the third argument. Using

FileContent = Replace( FileContent, "X" )

will remove all X from the string (replacing with nothing, i.e. not even a blank.)

https://msdn.microsoft.com/en-us/library/aa241892(v=vs.60).aspx

 

[peskywinnets]

Thanks for your input.

Having delved a lot deeper after asking my question, I'm now thinking this is to do with text file encoding. The originating text file is utf-8, I'm opening it with FSO like thus...

Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    Set objText = objFSO.OpenTextFile(strFullPathNameAndExtension, ForReading)

    I = 1
    While Not objText.AtEndOfStream
        strContent = objText.ReadLine
        If I > 3 Then
       [B]     strNewText = strNewText & Replace(strContent, "£","") & vbCrLf[/B]
        End If
        I = I + 1
    Wend
    
    objText.Close
    
    Set objText = objFSO.OpenTextFile(strFullPathNameAndExtension, ForWriting)

    objText.Write strNewText
    objText.Close
    Set objFSO = Nothing

(oops, just pasted an earlier iteration, which uses a different replace line - strNewText & Replace(strContent, "£","") , but the outcome is the same)

Apparently FSO doesn't handle UTF (discussion here about midway down in the subcomments https://stackoverflow.com/questions/13851473/read-utf-8-text-file-in-vbscript)

So it seems I have to use ADO ...which I've never used before, so a whole lot of research to do :-(

Oh, btw I tried the shortened version you mentioned...

FileContent = Replace( FileContent, "£" )

but the VBA compiler errored on that line

 

[peskywinnets]

Since my line of question relates to ADO, I've now opened a more appropriate thread...

https://www.access-programmers.co.uk/forums/showthread.php?p=1535004#post1535004