extracting the number from a cell

[cuddihy2k7]

Hi, I have a cell in my workbook that has <=1.0%

the number in this is subject to change and I need to be able to extract the number from it.

I have tried mid(range("A1"), 3, 5) but as it is subject to change it could be edited so that it reads 1.05%, in which case I would have to go back into edit the vba, is it possible to just extract the number part instead of the <, =, % in vba
Cheers guys

CUDDIHY2K7

 

[Venky]

Try this

Function removeSpecial(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
sSpecialChars = "\/:*?""<>|=%"
For i = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
Next
removeSpecial = sInput
End Function

and in the cell where you want only # enter =SUBSTITUTE(removeSpecial(A1)," ","")