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

 

[steve the fish]

That should work. Test your character is what you think it is:

MsgBox Asc(Mid(Range("A2"), 8, 1))

 

[asdafgafawaaa]

Thank you for the quick reply, the number is 45.

I can replace the is "Mid(Range("A2"), 8, 1)" with "", but when use chr(45), still not working! It's quite painful, haha~~~

Do you have another solution?

thank you again!

 

[asdafgafawaaa]

when i use below code to key in the value in Excel, it gave me a dash, the shorter one. Is this a bug????


Range("I3") = Chr(45)

 

[wideboydixon]

"-" = Chr(45)
"–" = Chr(150)
"—" = Chr(151)

Second and third are En-Dash and Em-Dash characters; perhaps you need those?

WBD

 

[Rick Rothstein]

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

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

I get the code for that character to be 8722 which means you will have to use the ChrW function for it, not the Chr one.

Range("B2") = Replace(Range("A2"), ChrW(8722), "")

 

[Fluff]

beaten 2it

 

[Stiuart_W]

When I run the code and paste YOUR text into cell B2 I get the same behaviour as you and the code reports the dash as ASCII 45. BUT when I manually edit the cell and remove and replace the dash with the one on my keyboard the code correctly removes it.

You presumably have some kind of non standard character in there

 

[asdafgafawaaa]

Great! Thank you a million, Rick.

It works!

 

[Stiuart_W]

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

TIA