Largest Value Within a Cell

[rompetelo]

Hi All,

A bit of a complicated one, I have a list with a number of values within a cell. I was wondering If there was a way to extract the largest value. See example below with the solution on the third column.

Bar Coffee Cat Dog Car Drive Blue Pink Orange Apple Green Tomato	1312 500 600 455 1 13 312671 1315 13126710 671 55 20	13126710
Orange Apple Green Tomato	13126710 671 55 20	13126710
Bar Coffee Cat Dog	1312 500 600 455	1312

<tbody>
</tbody>

many thanks,

Rompetelo

 

[rompetelo]

I've found this VBA code that sums up all the values within a cell - anyway to modify it to make it return the largest value?

Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double    Dim arr As Variant
    Dim xIndex As Long
    arr = Split(pWorkRng, xDelim)
    For xIndex = LBound(arr) To UBound(arr) Step 1
        SumNums = SumNums + VBA.Val(arr(xIndex))
    Next
End Function

 

[gallen]

This should work assuming they are separated with a line feed:

Sub GetLargest()
    Dim s As String
    Dim sSplit() As String
    Dim lMax As Long, l As Long
    
    
    s = Range("A1").Value 'Change as needed or set to active cell
    
    'Assuming the numbers are spearated by a line feed(Alt & Return in a cell):
    sSplit = Split(s, vbLf)
    
    For l = 0 To UBound(sSplit)
        If IsNumeric(sSplit(l)) Then
            If sSplit(l) > lMax Then lMax = sSplit(l)
        End If
    Next l
    
    MsgBox "Largest value is " & lMax
    
End Sub

 

[István Hirsch]

Give this formula a try (enter with Ctrl + Shift + Enter):

=MAX(IFERROR(--MID(SUBSTITUTE(CHAR(10)&B1,CHAR(10),REPT(" ",200)),200*ROW($1:$100),200),0))

 

[rompetelo]

Awesome! tried both and they work amazingly. For convenience Ill use Istvan as its a formula but thank you both!

You guys are geniuses!