How can I find character with ASCII code 160 in a string using VBA ?

[Grigoras]

In a cell I have a string.I want to eliminate from it all characters with ASCII code = 160.
In Excel formulas exist CODE formula to return ASCII code (160 in my case) for a character, but I d'ont find a way to do this with VBA.
Help, please!Thanks
:help

[shrivallabha]

One option will be to use formula in VBA. Place the original string in A1 and see the outcome in A2:
Public Sub FormulaRoute()
With Range("A2") 'Change to suit
.Formula = "=SUBSTITUTE(A1,CODE(160)," & Chr(34) & Chr(34) & ")" 'Change ref cell no A1
.Value = .Value
End With
End Sub
One more option would be:
Public Sub FormulaRoute2()
sString = Evaluate("=CODE(160)")
Range("A2").Value = Replace(Range("A1").Value, sString, "", 1, -1)
End Sub

[mohanvijay]

Try this

See help for Chr() function in object browser




Dim T_Str As String

T_Str = Range("a1").Value

T_Str = Replace(T_Str, Chr(160), "")

Range("a1").Value = T_Str

[shrivallabha]

Hi Mohan,
The code function and Chr function results differ on my machine. Are they the same on yours?
Try this bit on your machine:
Public Sub Test()
Range("A3").Value = Evaluate("=CODE(160)")
Range("A4").Value = Chr(160)
End Sub
But this works:
Dim T_Str As String

T_Str = Range("a1").Value

T_Str = Replace(T_Str, Asc(160), "")

Range("a1").Value = T_Str

[mohanvijay]

I think Grigors asking for remove ASCII=160 from particular range

if i guess correct my code works fine, if i did not please let me know



T_Str = Replace(T_Str, Asc(160), "")


Asc(160) returns 49 that the ASCII code for "1" and replace the "49" as "" in string T_Str

[mikerickson]

Asc return the ascii value of the string argument.

Asc(160) = Asc("160") = Asc("1") = 49

Chr returns the character specified by the ascii value argument

Chr(49) = "1"
Chr(160) = non-breaking-space
Chr(Asc(someString)) = Left(someString, 1)

If you want to know which character in a string is Chr(160), you could use code like


MsgBox InStr(1, myString, Chr(160))

[shrivallabha]

Thank you Mohan and Mike. I was not knowing it so got something new to learn.

That said, OP has mentioned Excel function CODE which returns 49. I would be interested to understand which one he is after.

[mikerickson]

CODE is a worksheet function that returns the same value as VBA's Asc function.

Worksheet CHAR and CODE , VBA Chr and Asc.

[shrivallabha]

Thanks again.

I think, OP is after CHAR and not CODE.