Here's a modified VB function that strips all non-numeric characters from a
string. (It's been modified to use the International(xlDecimalSeparator)
constant) You would need to determine the length of input after filtering,
and notify the user if it's not valid.
Function FilterNumber(Text As String, TrimZeros As Boolean) As String
' Filters out formatting characters in a number and trims any trailing
decimal zeros
' Requires the FilterString function
' Arguments: Text The string being filtered
' TrimZeros True to remove trailing decimal zeros
' Returns: String containing valid numeric characters.
Const sSource As String = "FilterNumber()"
Dim decSep As String, i As Long, sResult As String
' Retreive the decimal separator symbol
decSep = Application.International(xlDecimalSeparator) 'Format$(0.1, ".")
' Filter out formatting characters
sResult = FilterString(Text, decSep & "-0123456789")
' If there's a decimal part, trim any trailing decimal zeros
If TrimZeros And InStr(Text, decSep) > 0 Then
For i = Len(sResult) To 1 Step -1
Select Case Mid$(sResult, i, 1)
Case decSep
sResult = Left$(sResult, i - 1)
Exit For
Case "0"
sResult = Left$(sResult, i - 1)
Case Else
Exit For
End Select
Next
End If
FilterNumber = sResult
End Function
HTH
Regards,
Garry