excel formula to find maximum within same cell having multiple values of right most numbers

[vrsharma]

I want to find maximum and minimum for right most number from same cell having multiple values, below is the how data looks. This all values are in same cell, ex: in cell "A1" I want to extract rightmost number, like 1.00,1.5,1.5,1.0,2.00,1.50,1.0,1.00 and find max and min from it. I am currently using Excel 2016 version.

BAEK1928_TXL_1.00
44444922_FLR2X-A_1.5
44447922_FL2X-B_1.5
44444922_FL2X-B_1.0
BAEK1928_TXL_2.00
44444922_FLR2X-B_1.50
44444922_FLR2X-A_1.0
44444922_FLRY-B_1.00


Is there any excel formula or vba code for it?
I have tried some excel formula as below but it doesn't work as required.
=MAX(RIGHT(A1,LEN(A1)-FIND("_",A1,SEARCH("_",A1)+1))

 

[Fluff]

How about, for

Function GetMax(Txt As String) As Double
    Dim Sp As Variant
    Dim i As Long
    Dim x As Double
    
    Sp = Split(Txt, Chr(10))
    For i = 0 To UBound(Sp)
        x = Right(Sp(i), Len(Sp(i)) - InStrRev(Sp(i), "_"))
        If GetMax < x Then GetMax = x
    Next i
End Function

and

Function GetMin(Txt As String) As Double
    Dim Sp As Variant
    Dim i As Long
    Dim x As Double
    
    GetMin = 10 ^ 9
    Sp = Split(Txt, Chr(10))
    For i = 0 To UBound(Sp)
        x = Right(Sp(i), Len(Sp(i)) - InStrRev(Sp(i), "_"))
        If GetMin > x Then GetMin = x
    Next i
End Function

Used like

Book1
	A	B	C
1	BAEK1928_TXL_.001 44444922_FLR2X-A_1.5 44447922_FL2X-B_1.5 44444922_FL2X-B_1.0 BAEK1928_TXL_2.00 44444922_FLR2X-B_1.50 44444922_FLR2X-A_1.0 44444922_FLRY-B_1.00	2	0.001
Sheet2

Cell Formulas
Range	Formula
B1	B1	=GetMax(A1)
C1	C1	=getmin(A1)

 

[sandy666]

with Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Extract = Table.TransformColumns(Split, {{"Column1", each Text.AfterDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
    One = Table.AddColumn(Table.TransformColumnTypes(Extract,{{"Column1", type number}}), "Custom", each 1),
    Group = Table.Group(One, {"Custom"}, {{"min", each List.Min([Column1]), type number}, {"max", each List.Max([Column1]), type number}}),
    ROC = Table.SelectColumns(Group,{"min", "max"})
in
    ROC

 

[vrsharma]

How about, for

Function GetMax(Txt As String) As Double
    Dim Sp As Variant
    Dim i As Long
    Dim x As Double
   
    Sp = Split(Txt, Chr(10))
    For i = 0 To UBound(Sp)
        x = Right(Sp(i), Len(Sp(i)) - InStrRev(Sp(i), "_"))
        If GetMax < x Then GetMax = x
    Next i
End Function

and

Function GetMin(Txt As String) As Double
    Dim Sp As Variant
    Dim i As Long
    Dim x As Double
   
    GetMin = 10 ^ 9
    Sp = Split(Txt, Chr(10))
    For i = 0 To UBound(Sp)
        x = Right(Sp(i), Len(Sp(i)) - InStrRev(Sp(i), "_"))
        If GetMin > x Then GetMin = x
    Next i
End Function

Used like

Book1
	A	B	C
1	BAEK1928_TXL_.001 44444922_FLR2X-A_1.5 44447922_FL2X-B_1.5 44444922_FL2X-B_1.0 BAEK1928_TXL_2.00 44444922_FLR2X-B_1.50 44444922_FLR2X-A_1.0 44444922_FLRY-B_1.00	2	0.001
Sheet2

Cell Formulas
Range	Formula
B1	B1	=GetMax(A1)
C1	C1	=getmin(A1)

Thank you Fluff, it worked very well

 

[vrsharma]

with Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Extract = Table.TransformColumns(Split, {{"Column1", each Text.AfterDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
    One = Table.AddColumn(Table.TransformColumnTypes(Extract,{{"Column1", type number}}), "Custom", each 1),
    Group = Table.Group(One, {"Custom"}, {{"min", each List.Min([Column1]), type number}, {"max", each List.Max([Column1]), type number}}),
    ROC = Table.SelectColumns(Group,{"min", "max"})
in
    ROC

View attachment 1624

Thanks sandy, for this interesting solution.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[FormR]

Hi, here is a formula you could also try, note that this is currently hard coded for a maximum of 10 values in any one cell, this maximum can be increased or made dynamic if needed.

The formula makes use of a technique for parsing delimited strings which can be found here:

Get Field from Delimited Text String

How many times have you had a delimited text string (say "one,two,three,four" where the delimiter is a comma) and you wanted a formula to retrieve one of the delimited values, say, the third one. Personally, I have wondered for years why such a function is not built into Excel but, alas, it...

www.excelfox.com

Book1
	A	B	C
1	BAEK1928_TXL_.001 44444922_FLR2X-A_1.5 44447922_FL2X-B_1.5 44444922_FL2X-B_1.0 BAEK1928_TXL_2.00 44444922_FLR2X-B_1.50 44444922_FLR2X-A_1.0 44444922_FLRY-B_1.00	0.001	2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=AGGREGATE(15,6,0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",LEN(A1))),{1,2,3,4,5,6,7,8,9,10}*LEN(A1)-(LEN(A1)-1),LEN(A1))),"_",REPT(" ",15)),15)),1)
C1	C1	=AGGREGATE(14,6,0+TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",LEN(A1))),{1,2,3,4,5,6,7,8,9,10}*LEN(A1)-(LEN(A1)-1),LEN(A1))),"_",REPT(" ",15)),15)),1)