Replace 'Inserted' Symbol with standard text

[DJ Peterson]

I have a spreadsheet that is processed in a VBA program that takes the data line-by-line and after validation, uploads it to a server. I'm using ActiveX Data Objects 2.8 to upload to the server. It seems that either ADO or the server does not like any 'Inserted characters'. I need to search each cell to see if there is any symbol in the string that was placed there by using 'Insert Symbol' in Excel. If found, then I will replace the symbol with text representing that symbol (character). For Instance I need to find any occurance of the Omega symbol 'Ω' and replace it with Ohm. I can't figure out how to use InStr() or Replace() to find it. The Omega symbol is a hex 2126 or decimal 8486. I've tried chr(8486) and chrW(8486) but none of these work. An example of the string data in a cell is 'R1 10kΩ, R2 10kΩ, 50V, NPN+NPN'.

Any one have any ideas?

Thanks

 

[pgc01]

Hi

I wrote in A1:

R1 10kΩ, R2 10kΩ, 50V, NPN+NPN

and executed:

Sub test()
Dim s

s = Replace(Range("A1").Value, ChrW(8486), "Ohm")
MsgBox s

End Sub

the message box displayed:

R1 10kOhm, R2 10kOhm, 50V, NPN+NPN

Please try.

 

[Fluff]

The standard search/replace using Ctrl H works for me.
Just copy/paste the symbol into the find what box.

 

[DJ Peterson]

To pgc01: - You are right. I was so focused on the function that I did not see that I was putting the result in the wrong cell; I did not have the Offset for the resulting cell. My assumption was that it was not working.

ActiveCell.Value = Replace(ActiveCell.Offset(0,16).value, chrW(8486), "Ohm")

To Fluff: - I was asking for a VBA solution not an interactive solution, but thanks anyway.

 

[pgc01]

I'm glad you figured it out.