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
1	2~~5~~18~~	18		18
2	-11~~-60~~-3	#N/A		-3
3	55~~80	80		80
4	2~~5~~18~~	18		18
5	20~~50~~60	60		60
6	30~~ 60~~ 70	70		70
7	2~~5~~18~~-15	18		18
8

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B1 =MATCH(1000,INDEX(FIND(","&ROW(INDIRECT("1:999"))&",",","&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"~",",0,"),",,",",0,")&","),0)) D1 =HighVal(A1) <tbody> </tbody>

<tbody>
</tbody>

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))