How to find the highest and lowest values in a sin

问题:

I am trying to find the highest and lowest values of a single cell.

There are many variations to the strings

Cell E9

a)63Ra b)64Ra c)65Ra d)62Ra e)61Ra f)63Ra g)60Ra h)62Ra

Cell E10

a)45° b)44.5° c)45° d)45° e)45.2° f)44.7°

Cell E11

a)Ø14.290 b)Ø14.286 c)Ø14.283

Cell F9 > 65

Cell G9 > 60

Cell F10 > 44.5

Cell G10 > 45.2

Cell F11 > 14.290

Cell G11 > 14.283

I found a similar problem but I am not sure how to tweak the formula to my needs. Extract maximum number from a string

回答1:

Try this vba code

 Option Explicit
Sub cut_my_number_Please()
Dim reg As Object
Dim MY_match
Dim Matches
Dim my_max As Double: my_max = -1
Dim i%, lr%: lr = Cells(Rows.Count, 5).End(3).Row
Dim k%: k = 6
Union(Range("D9:D50"), Range("F9:R50")).ClearContents
Set reg = CreateObject("VBscript.RegExp")
With reg
.Pattern = "([\d\.]+)"
.Global = True
End With
 For i = 9 To lr
    If reg.test(Range("E" & i)) Then
      Set Matches = reg.Execute(Range("E" & i))
       For Each MY_match In Matches
         Cells(i, k) = MY_match * 1
         If MY_match * 1 >= my_max Then my_max = MY_match * 1
        k = k + 1
       Next
    End If
     Cells(i, "D") = my_max
     my_max = -1
      k = 6
 Next i
End Sub

回答2:

I have an answer for Cell E9. Hope this will help you to get an idea.

Please insert module and copy the below VBA code into that.

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

Then use below function to identify highest value from the same.

=MAX(IF(LEN(SplitText(E9,TRUE))>1,+MID(SplitText(E9,TRUE),1,2),0),IF(LEN(SplitText(E9,TRUE))>3,+MID(SplitText(E9,TRUE),3,2),0),IF(LEN(SplitText(E9,TRUE))>5,+MID(SplitText(E9,TRUE),5,2),0),,IF(LEN(SplitText(E9,TRUE))>7,+MID(SplitText(E9,TRUE),7,2),0),IF(LEN(SplitText(E9,TRUE))>9,+MID(SplitText(E9,TRUE),9,2),0),IF(LEN(SplitText(E9,TRUE))>11,+MID(SplitText(E9,TRUE),11,2),0),IF(LEN(SplitText(E9,TRUE))>13,+MID(SplitText(E9,TRUE),13,2),0),IF(LEN(SplitText(E9,TRUE))>15,+MID(SplitText(E9,TRUE),15,2),0),IF(LEN(SplitText(E9,TRUE))>17,+MID(SplitText(E9,TRUE),17,2),0),IF(LEN(SplitText(E9,TRUE))>19,+MID(SplitText(E9,TRUE),19,2),0),IF(LEN(SplitText(E9,TRUE))>21,+MID(SplitText(E9,TRUE),21,2),0))

For lowest value use MIN().