excelvbamacoschr

vba search replace character

I'm trying to prepare a spreadsheet for a report in excel vba. Unforturnately there are some wierd characters here that need to be replaced. Easy enough, except for this chracter:

  ¦

I can't seem to be able to paste that character into the editor into a string replace function. When I try, the output is _. I then thought to refer to it by it's Chr code. A quick look up said it was Chr(166). http://www.gtwiki.org/mwiki/?title=VB_Chr_Values

Replace(s, "â€" + Chr(166), "...")

But this is not that character at all (at least on Mac excel). I tried:

For i = 1 To 255
   Debug.Print Chr(i)
Next i

And I didn't see this character anywhere. Does anyone know how I can reference this character in vba code in order to replace it?

Solution

  • Not sure if is available for , but you could simplify your existing code greatly as below.

    Uses a sample Strin

    Dim strIn As String
strIn = "1â€1â€x123"
Do While InStr(strIn, "â€") > 0
Mid$(strIn, InStr(strIn, "â€"), 3) = "..."
Loop