Excel 2016提取单元格最右侧数值并求最值的方法

Hey there! Let's work through your Excel 2016 problem—you need to pull the rightmost numeric value from each group in a single cell, then calculate the max and min of those values. Your current formula isn't cutting it because some entries have wonky spacing, inconsistent underscores, or no numeric value at all. Let's cover two solid approaches: a robust array formula (no macros needed) and a flexible VBA custom function.

Array Formula Approach (Excel 2016 Compatible) #

Since Excel 2016 doesn't support dynamic arrays, we'll use an array formula—you'll need to enter it with Ctrl+Shift+Enter instead of just Enter.

To Get the Maximum Value: #

=MAX(IFERROR(VALUE(RIGHT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1))),LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1))))-FIND("~_",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1))),SEARCH("~_",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1)))+1))),"")

To Get the Minimum Value: #

Just replace MAX with MIN in the formula above:

=MIN(IFERROR(VALUE(RIGHT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1))),LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1))))-FIND("~_",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1))),SEARCH("~_",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*LEN(A1)+1,LEN(A1)))+1))),"")

How This Works:

1. Splits the cell content: Uses SUBSTITUTE and MID to split the text into individual groups separated by spaces.
2. Cleans up groups: TRIM removes extra spaces from each group (like in entries such as BIN76822 _THIN_1.00).
3. Finds the last underscore: Uses FIND with a tilde (~) to escape the underscore (since _ is a wildcard) and SEARCH to locate the final underscore in each group.
4. Extracts and converts values: Pulls the text after the last underscore, converts it to a number with VALUE, and IFERROR filters out any groups that don't have a valid numeric value.
5. Calculates max/min: Wraps the whole thing in MAX or MIN to get your desired result.

VBA Custom Function Approach (More Flexible) #

If you're comfortable using macros, this method is easier to read and maintain, especially if you need to handle edge cases regularly.

Step 1: Add the VBA Code #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer > Insert > Module.
3. Paste this code into the module:

Function GetNumericValues(rng As Range) As Variant
    Dim cellContent As String
    Dim items() As String
    Dim result() As Double
    Dim i As Integer, j As Integer
    Dim lastUnderlinePos As Integer
    Dim numStr As String
    Dim numVal As Double
    
    cellContent = Trim(rng.Value)
    If cellContent = "" Then
        GetNumericValues = Empty
        Exit Function
    End If
    
    ' Split the cell content into individual groups using space as delimiter
    items = Split(cellContent, " ")
    
    ' Initialize array to hold valid numeric values
    ReDim result(0 To UBound(items))
    j = 0
    
    For i = LBound(items) To UBound(items)
        ' Skip empty entries caused by multiple spaces
        If Trim(items(i)) <> "" Then
            ' Find the position of the last underscore in the group
            lastUnderlinePos = InStrRev(items(i), "_")
            If lastUnderlinePos > 0 Then
                numStr = Mid(items(i), lastUnderlinePos + 1)
                ' Check if the extracted text is a valid number
                If IsNumeric(numStr) Then
                    numVal = CDbl(numStr)
                    result(j) = numVal
                    j = j + 1
                End If
            End If
        End If
    Next i
    
    ' Resize the array to match the actual number of valid values
    If j > 0 Then
        ReDim Preserve result(0 To j - 1)
        GetNumericValues = result
    Else
        GetNumericValues = Empty
    End If
End Function

Step 2: Use the Function in Excel #

• For the maximum value: =MAX(GetNumericValues(A1))
• For the minimum value: =MIN(GetNumericValues(A1))

How This Works:

• The function takes a cell as input, splits its content into groups by spaces, then loops through each group.
• It finds the last underscore in each group, extracts the text after it, and checks if it's a valid number.
• Valid numbers are stored in an array, which is returned to Excel. You can then use standard MAX/MIN functions to compute the desired values.
• It handles edge cases like extra spaces before underscores, multiple underscores per group, and groups with no numeric values (which are skipped entirely).

内容的提问来源于stack exchange，提问作者Vishal