Below are two functions you can use to find the maximum tolerance (MaxTol)
and the minimum tolerance (MinTol). These functions can be called from
within your own VB code or, if placed in a Module, directly from the
worksheet (as placing them in a Module makes them into a UDF). I also
created an error condition return value (#BADFORMAT!) that will be returned
if the variable tolerance (VarTol) passed into the function cannot be
resolved to one of the three formats or if a non-numeric value is contained
in one of the number parts.

Rick

Function MaxTol(VarTol As String) As Variant
Dim Parts() As String
On Error GoTo BadFormat
If VarTol Like "* [Ss][Tt] *" Then
MaxTol = CDbl(Split(VarTol, "ST", , vbTextCompare)(1))
Exit Function
End If
If VarTol Like "* +-*" Then
Parts = Split(VarTol, "+-")
MaxTol = CDbl(Parts(0)) + CDbl(Parts(1))
Exit Function
End If
If VarTol Like "* */*" Then
MaxTol = CDbl(Split(VarTol)(0)) + CDbl(Split(Split(VarTol)(1), "/")(0))
Exit Function
End If
BadFormat:
MaxTol = "#BADFORMAT!"
End Function

Function MinTol(VarTol As String) As Variant
Dim Parts() As String
On Error GoTo BadFormat
If VarTol Like "* [Ss][Tt] *" Then
MinTol = CDbl(Split(VarTol, "ST", , vbTextCompare)(1))
Exit Function
End If
If VarTol Like "* +-*" Then
Parts = Split(VarTol, "+-")
MinTol = CDbl(Parts(0)) - CDbl(Parts(1))
Exit Function
End If
If VarTol Like "* */*" Then
MinTol = CDbl(Split(VarTol)(0)) + CDbl(Split(Split(VarTol)(1), "/")(1))
Exit Function
End If
BadFormat:
MinTol = "#BADFORMAT!"
End Function



"pallaver" wrote in message
...
I don't know enough about VBA yet to do this without help.

I have a column of variable tolerances which have one of the following
forms:

5 ST 5.3 (aka number, space, "ST", space, desired number)
5 +-6 (aka number, space, +- symbol, another numberm)
5 +4/-2 (aka number, space, positive number, /, negative number)

I need to be able to extract the tolerance numbers in order to use
them for conditional formatting.

I would want the following from the above:
5.3 (there is a set tolerance off this number, so all I need is this
number, I don't need 5 at all).
5 and 6 to turn into -1 to 11 (the max/min of the tolerance).
5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance).

This surely involves finding spaces, /, etc., and determining what is
what, but I'm having a lot of trouble getting things to work in VBA.

Suggestions and existing code to improve much appreciated.

Thanks, NP