VBA find and replace ?

[mikeprice53]

I am trying to find text using vba method and replace with nothing to wipe out the value.

there are other things already in the cell so thats why I need to find and relace but I need to do it with a button hence hte VBA way !

Please help

Thanks

 

[MClifton]

Has anyone been able to do a find and replace for all foreign currencies? I need the currency symbol removed from cells as it causes the number to read as text. I have a current method that detects the currency symbol in an easy to locate cell, but am having trouble with the Thai Baht symbol (฿). VBA is reading it as character 63 or "?". When it does the replace, it replaces the entire contents of the cell. I put in a patch that if the currency symbol = chr(63) to replace the contents of the cell with .Value = Right(.Value, Len(.Value) - 1). This "works", but isn't ideal as some sheets have hundreds of rows and hundreds of columns.

 

[Rick Rothstein]

Has anyone been able to do a find and replace for all foreign currencies? I need the currency symbol removed from cells as it causes the number to read as text. I have a current method that detects the currency symbol in an easy to locate cell, but am having trouble with the Thai Baht symbol (฿). VBA is reading it as character 63 or "?". When it does the replace, it replaces the entire contents of the cell. I put in a patch that if the currency symbol = chr(63) to replace the contents of the cell with .Value = Right(.Value, Len(.Value) - 1). This "works", but isn't ideal as some sheets have hundreds of rows and hundreds of columns.

This formula will find the first digit in the cell and return it and everything after it as a Text value...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

If the only thing in the cell is text (your currency symbol) followed by a number and you want that number to be a real number, add 0 to the above formula...

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

 

[Fluff]

Alternatively as a test try

ActiveCell.Replace ChrW(3647), ""

 

[MClifton]

Thanks Rick, however I have a current working method that can remove the currency symbol cell by cell. I'm looking for more of a find and replace method that can do the entire sheet at once. Going cell by cell is very time consuming on larger documents.

 

[MClifton]

Thanks Fluff, that is exactly what I needed. It replace only the currency symbol the whole sheet at once.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Fluff]

What I should have also said in post#16, but forgot!
My suggestion was a test to check if I had the correct symbol.
The search/replace remembers your previous settings so this would be a better way of doing it

ActiveCell.Replace ChrW(3647), "", xlPart, , , , False, False