replace Chr(45) in VBA

[asdafgafawaaa]

Hi,

One small section of VBA code doesn't work for the "−", this is not a regular dash.

I use the ASC function to get the ASCII code which is chr(45), then use the replace function to replace with "" so as to remove it. But it doesn't work at all, can anyone help me with this, much appreciate it!

For example for number 9999999−6 at A2, I want to put the new value at B2.

Sub test()

Range("B2")=replace(range("A2"),CHR(45),"")

End Sub

Thank you very much!

Jack

 

[asdafgafawaaa]

Thank you everyone for your advice!

 

[Rick Rothstein]

How do you find that code Rick if you don't mind me asking?

I copied the character from the email and then pasted it into cell A1 on whatever sheet was active... then I executed this code line in the VB editor's Immediate Window...

? AscW(Range("A1"))

 

[wideboydixon]

Paste the value into cell A1, bring up the VBA editor and type the following in the immediate window:

? ascw(mid(range("a1").value,8,1))

It's a Unicode code point called "Minus Sign". You can use the Windows built-in Character Map application to explore characters and code points. You'll also find it two columns across and three rows done in this code chart:

http://www.unicode.org/charts/PDF/U2200.pdf

WBD

 

[Rick Rothstein]

How do you find that code Rick if you don't mind me asking?

I copied the character from the email and then pasted it into cell A1 on whatever sheet was active... then I executed this code line in the VB editor's Immediate Window...

? AscW(Range("A1"))

I meant to include a little more info with the above, but I got a phone call and by the time I hung up, the time to edit the comment expired. Here is the complete text of what I wanted to post...

I copied the character from the email and then pasted it into cell A1 on whatever sheet was active... then I executed this code line in the VB editor's Immediate Window...

? AscW(Range("A1"))

The reason I did it that way is because the VB editor cannot display Unicode characters above ASCII code 255; however, it does make some substitutions for "similar" characters. So while you will get a question mark displayed for most numbers in the ChrW function, for example...

? ChrW(8721)

it appears the VB editor sees character code 8722 is similar to a dash and so this code...

? ChrW(8722)

actually prints out a real ASCII dash (code 45), again, apparently because it "knows" that code 8722 is a type of dash.