NEEDS A CORRECTION.
After reading one of your posts, I realize that the constant
numericCharacters needs to have the decimal point added to it. If you try
that code out, change that line to read:
Const numericCharacters = "0123456789+-."
The change in the sequence of characters in it will probably improve
performance just a tiny bit since the probability of encountering a digit is
higher than one of the +, - or . symbols.

"JLatham" wrote:

The following code will work for the examples you've provided, given that all
entries are exactly like the ones you've provided, it should work for a list
of similar type entries. There is a good chance of it failing or locking up
in places if the entries in your list don't follow the same format of your
examples.

I leave it for the student to investigate Help on any unfamilair VB commands
used.

Sub ParseEntries()
Dim listOfEntries As Range
Dim anyEntry As Range
Const numericCharacters = "+-0123456789"
Const specialText = " ST "
Const separator1 = " " ' single space
Const separator2 = "/"
Const separator3 = "+-"
Dim initialValue As String
Dim result1 As String
Dim result2 As String
Dim position As Integer

'get address of cells
'with entries in them
'assumes it starts at
'A1 and continues as an
'unbroken list down that column
' change "A1:" to have address of first entry in the list
' change the A column references as required.
Set listOfEntries = Range("A1:" & _
Range("A" & Rows.Count).End(xlUp).Address)
'now work through each cell in the group
For Each anyEntry In listOfEntries
'pick up the number at the beginning
'of the entry
initialValue = Left(anyEntry, InStr(anyEntry, separator1) - 1)
result1 = "" ' clear previous results
result2 = "" ' clear previous results
If InStr(anyEntry, specialText) Then
'contains " ST ", all we want is the
'number at the far right
result1 = Right(anyEntry, Len(anyEntry) - _
InStrRev(anyEntry, separator1))
result2 = result1 ' so we have something later
initialValue = 0 ' so the math works later!
Else
'do we have an entry with "+-"
'presumably followed by single numeric value?
If InStr(anyEntry, separator3) Then
'yes it has "+-" in it
result1 = Right(anyEntry, Len(anyEntry) - _
InStrRev(anyEntry, separator3) - 1)
result2 = "-" & result1 ' make the negative side
Else
'we must assume it is an entry
'with the / separator in it
'get the value on the right side of the "/"
For position = InStr(anyEntry, separator2) To Len(anyEntry)
If InStr(numericCharacters, Mid(anyEntry, position, 1)) Then
result1 = result1 & Mid(anyEntry, position, 1)
End If
Next ' position loop end
'get the value on the left side of the "/"
'work left to right from the "/"
For position = (InStr(anyEntry, separator2) - 1) To 1 Step -1
If InStr(numericCharacters, Mid(anyEntry, position, 1)) Then
result2 = result2 & Mid(anyEntry, position, 1)
End If
Next
End If
End If ' end of 1st/outer If..Then..Else
'display the results,
If (Val(initialValue) + Val(result1) <= _
Val(initialValue) + Val(result2)) Then
'one limit in cell 1 column to the right of the entry
anyEntry.Offset(0, 1) = Val(initialValue) + Val(result1)
'other limit in cell 2 columns to the right of the entry
anyEntry.Offset(0, 2) = Val(initialValue) + Val(result2)
Else
'one limit in cell 1 column to the right of the entry
anyEntry.Offset(0, 1) = Val(initialValue) + Val(result2)
'other limit in cell 2 columns to the right of the entry
anyEntry.Offset(0, 2) = Val(initialValue) + Val(result1)
End If
Next
End Sub


"pallaver" wrote:

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