Need help for "Format" function

[avadhutd2]

Hi,

I have a question regarding the format function.

My requirement to verify an ID and fetch data from another sheet corresponding to that ID.

Hence here I need to format a number which is seen as 1-23456 or (1)23456 to 123456 as that will help me for comparison.
I tried the code below, however that is not giving me the results anticipated.

If (IsNumeric(ActiveSheet.Range("H" & lngCtr))) Then
ActiveSheet.Range("H" & lngCtr) = _
Format(ActiveSheet.Range("H" & lngCtr), "000000")
End If

Can anyone give me further inputs?

Thanks in advance!

[Bob Phillips]

[vba]

If (IsNumeric(ActiveSheet.Range("H" & lngCtr))) Then

With ActiveSheet.Range("H" & lngCtr)

.Value = Replace(Replace(Replace(.Value, _
"-", ""), _
"(", ""), _
")", "")
End With
End If
[/vba]

[avadhutd2]

Thanks for the reply...When I tried ID as 1-23456 or (1)23456, I am still getting same result.

[Bob Phillips]

I missed the IF bit, which stops it working because the value is not numeric

[vba]

With ActiveSheet.Range("H" & lngCtr)

.Value = Replace(Replace(Replace(.Value, _
"-", ""), _
"(", ""), _
")", "")
End With
[/vba]