Maximum / Highest Value of a cell

[RAMU]

Dear Friends,

I have a workbook where cells are carrying values like the following:

2~~5~~18~~-15
-11~~-60~~-3
55~~80

More surprisingly sometimes there is a space after~~ sometimes there isn't like this
20~~50~~60
30~~ 60~~ 70

I badly need a formula which will give me the highest value of those cells
That means:
2~~5~~18~~-15 = Desired Result is 18

But the formula will work irrespective the space.
Thanks in advance
Regards
RAMU

 

[MartyS]

Dear Friends,

I have a workbook where cells are carrying values like the following:

2~~5~~18~~-15
-11~~-60~~-3
55~~80

More surprisingly sometimes there is a space after~~ sometimes there isn't like this
20~~50~~60
30~~ 60~~ 70

I badly need a formula which will give me the highest value of those cells
That means:
2~~5~~18~~-15 = Desired Result is 18

But the formula will work irrespective the space.
Thanks in advance
Regards
RAMU

Excel 2010

	A	B	C	D
2~~5~~18~~
-11~~-60~~-3
55~~80
2~~5~~18~~
20~~50~~60
30~~ 60~~ 70
2~~5~~18~~-15

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]80[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=MATCH(1000,INDEX(FIND(","&ROW(INDIRECT("1:999"))&",",","&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"~",",0,"),",,",",0,")&","),0))[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=HighVal(A1)[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The Excel Formula works great unless you only have negative numbers.

The UDF works regardless of positive or negative numbers


If you're interested in the UDF: Create a new Module within the VBA editor and paste the following:

Public Function HighVal(StrIn As String) As Integer


    Dim TmpStr As String
    Dim StrArray() As String, IntArray() As Integer
    Dim Cntr As Integer, IntCntr As Integer
    
    
    
    TmpStr = WorksheetFunction.Substitute(StrIn, " ", "")
    TmpStr = WorksheetFunction.Substitute(TmpStr, "~", ",`,")
    TmpStr = WorksheetFunction.Substitute(TmpStr, ",,", ",`,")
    
    If Right(TmpStr, 1) = "," Then TmpStr = Left(TmpStr, Len(TmpStr) - 1)


    StrArray = Split(TmpStr, ",")
    
    For Cntr = 1 To UBound(StrArray)
        If StrArray(Cntr) <> "`" Then
            IntCntr = IntCntr + 1
            ReDim Preserve IntArray(1 To IntCntr)
            IntArray(IntCntr) = CInt(StrArray(Cntr))
        End If
    Next Cntr


    HighVal = WorksheetFunction.Max(IntArray)


End Function

Hope this helps!

 

[RAMU]

Dear Friend,

Thnx for your help, but my file is so heavy that I don't want to use VBA. Is it not at all possible using formula only ?
But unfortunately few cells are carrying negative value only like -9~~-22~~-3 & -2~~-60~~-85.
If possible please help, otherwise I have to go with VBA ultimately.

 

[Aladin Akyurek]

In A2 control+shift+enter, not just enter, and copy down:

=MAX(eval("{"&SUBSTITUTE(TRIM(SUBSTITUTE(A3,"~"," "))," ",",")&"}"))

For this formula to work, you need to install the following udf as a module, using Alt+F11...

_________________________

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
_________________________​

 

[RAMU]

Thanks for helping me. It works gr8...!!!

 

[Haluk]

Although the problem is solved by Aladin Akyurek, here is a formula approach .(If you dont have too much data within a cell)

Sample file can be downloaded from Google Drive using the link below;

https://drive.google.com/open?id=1_EBeyFEwQV7OhK3UBc2tONLcaRoUy1zn

[url=https://hizliresim.com/766BBL][/URL]

 

[Haluk]

The following array formula can handle if the length of the data in the cells are bit longer ....

(should be entered by Ctrl+Shift+Enter)

=MAX(IF(ISERR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(TRIM(A1),"~"," ")," ",REPT(" ",100)),100*ROW($1:$900),100))+0),"-2,2251 E-308",TRIM(MID(SUBSTITUTE(SUBSTITUTE(TRIM(A1),"~"," ")," ",REPT(" ",100)),100*ROW($1:$900),100))+0))