replace \u0087 with "" using VBA-CodePudding

I have data in an Excel sheet that has been downloaded by a Power Query and there are \u0087 characters

I am trying to replace the character \u0087 with "" with VBA:

.Replace "‡", ""
.Replace "u0087", ""
.Replace what:=Chr(135), replacement:=""

But it is not replacing even though this method work fine for other characters like Em-Dash

.Replace what:="–", replacement:="-"
.Replace what:=Chr(150), replacement:=Chr(45)

Why this is not working? How to make it work

CodePudding user response：

If you are open to do it in Power Query, you just need to select your column in the Power Query Editor and go to Transform -> Replace Values -> Replace Values.

On value to find, add your symbol: ‡

On Replace With, keep it blank as it is. Then click ok.

Your code will be like = Table.ReplaceValue(Source,"‡","",Replacer.ReplaceText,{"Column1"}) and the result in my example is the following.

If you still prefer to use VBA, this code works for me: ActiveCell.Replace What:="‡", Replacement:=""