remove special characters from a cell in VB6

[5star]

I need to remove special characters from a cell .The cell value is $1,234.50.
I want 1234.50 as result .
Pls help me.

regards,
5star

[Killer42]

I need to remove special characters from a cell .The cell value is $1,234.50.
I want 1234.50 as result.

Some questions for you...

• As far as VB6 is concerned, what is a "cell"? I'm guessing maybe this has something to do with an Excel worksheet?
• What have you attempted so far?
• How certain are you that the dollar sign and comma are actually stored in the cell? Perhaps the cell simply holds a number, but is displaying it in a nicely-formatted way.

[5star]

I have to format a excel sheet using VB, in which a column in excel consists of amount in the format $1,234.50 . I need to format it by removing $ and , so that i will get 1234.50 value.

i tried the following syntax:

xlsheet.Cells(1, 1).NumberFormat = "text"

Error I got is :Application-defined or object-defined error.

Pls help me to get out of this pblm.

[Killer42]

I don't think changing the number format will help, because it isn't a number to start with. How about setting .Value to Cleaned(.Value). And in a module somewhere, add this function...

Public Function Cleaned(ByVal Src As String) As String
  Dim L As Long, I As Long, Char As String * 1
  L = Len(Src)
  For I = 1 To L
    Char = Mid$(Src, I, 1)
    Select Case Char
      Case "0" To "9", "."
        Cleaned = Cleaned & Char
    End Select
  Next
End Function

This will take the value and strip off everything except numeric digits and full stops. I have no idea what will happen if the cell already contains a number. You can find that out for yourself. :)