How to find max and min in an alphanumeric data ar

Question

I have the following kind of data

A1          B1              C1              D1
II Cm2 447  I Phy Hn 60     II Cm2 457      I Phy Hn 70
II Cm2 448  I Phy Hn 61     II Cm2 458      I Phy Hn 71
II Cm2 449  I Phy Hn 62     II Cm2 459      I Phy Hn 72
II Cm2 450  I Phy Hn 63     II Cm2 460      I Phy Hn 73
II Cm2 451  I Phy Hn 64     II Cm2 461      I Phy Hn 74
II Cm2 452  I Phy Hn 65     II Cm2 462      I Phy Hn 75
II Cm2 453  I Phy Hn 66     II Cm2 463      I PHY ml 76
II Cm2 454  I Phy Hn 67     II Cm2 464      I PHY ml 77
II Cm2 455  I Phy Hn 68     II Cm2 465      I PHY ml 78
II Cm2 456  I Phy Hn 69     II Cm2 466      I PHY ml 79

Need to find Minimum and Maximum corresponding to 'II Cm2', 'I Phy Hn' and 'I PHY ml'

Please Help.

The solution I am looking at

Grid           Min      Max
II Cm2         447      466
I Phy Hn        60       75
I Phy ml        76       79

EDIT: I have tried several options available over internet, using VLOOKUP, RIGHT, INDEX,... But none gives me intended answer. I would prefer a solution without using helper columns. If need to use helper columns, please suggest a solution without requiring modification of my cols A:D.

For Min I have a solution MIN(VLOOKUP("II Cm2",$AE$4:$AF$171,2,0), but it requires a helper column of extracted numbers from a single column of my original data.

Solutions using macro/script also welcome.

Answer 1

If you need to extract only Min/Max number from range, please use this formula in cell G2 and drag it to the bottom of values you entered in range F2:F5. =MIN(IF(ISNUMBER(SUBSTITUTE($A$2:$D$500,$F2,"")+0),SUBSTITUTE($A$2:$D$500,$F2,"")+0)) you must enter it using CTRL+SHIFT+ENTER combination since it it array formula. For Max just replace Min in formula.

Answer 2

You can read about how to create a formula that takes a range of cells as a parameter here.

This is one way to return just the numbers from a string.

Here is a function that looks for one string within another, backwards.

This tells you about changing a string into a number.

Below is one way that you could put them together to find your 'max' value:

Function MaxValueOfRange(rng As Range) As Integer
    Dim c As Range, strIn As String, intRet As Integer
    For Each c In rng 'loop through each cell in range
        strIn = Right(c, Len(c) - InStrRev(c, " ")) 'get everything after the last 'space'
    Next
    If Val(strRet) > intRet Then intRet = Val(strRet) 'convert it to number and keep the biggest one
    MaxValueOfRange = intRet 'return the biggest number
End Function