Excel 2007 find the largest number in text string

Question

I have Excel 2007. I am trying to find the largest number in a cell that contains something like the following:

[[ E:\DATA\SQL\SY0\ , 19198 ],[ E:\ , 18872 ],[ E:\DATA\SQL\ST0\ , 26211 ],[ E:\DATA\SQL\ST1\ , 26211 ],[ E:\DATA\SQL\SD0\ , 9861 ],[ E:\DATA\SQL\SD1\ , 11220 ],[ E:\DATA\SQL\SL0\ , 3377 ],[ E:\DATA\SQL\SL1\ , 1707 ],[ E:\DATA\SQL_Support\SS0\ , 14375 ],[ E:\DATA\SQL_Support\SS1\ , 30711 ]]

I am not a coder but I can get by with some basic instructions. If there is a formula that can do this, great! If the best way to do this is some sort of backend code, just let me know. Thank you for your time.

I do have the following formula that almost gets me there:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN($A$2))),1))*ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)

With a cell that contains a string like above, it will work. However, with a string that contains something like:

[[ E:\DATA\SQL\SY0\ , 19198.934678 ],[ E:\ , 18872.2567 ]]

I would end up with the value of 19198934678 as the largest value.

Answer 1

You can use this UDF:

Function MaxInString(rng As String) As Double
Dim splt() As String
Dim i&

splt = Split(rng)
For i = LBound(splt) To UBound(splt)
    If IsNumeric(splt(i)) Then
        If splt(i) > MaxInString Then
            MaxInString = splt(i)
        End If
    End If
Next i
End Function

Put this in a module attached to the workbook. NOT in the worksheet or ThisWorkbook code.

Then you can call it like any other formula:

=MaxInString(A1)