Excel 2007 find the largest number in text string

问题:

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.

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

回答2:

If there is always a space before and after, you can use this formula. The formula is an array formula and must be confirmed by holding down ctrl + shift while hitting enter

With your string in A1:

=MAX(IFERROR(--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),IF(seq=1,1,(seq-1)*99),99)),0))

seq is a defined name that refers to:

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,255,1))

If a VBA UDF is preferable, I suggest the following. The Regex will match anything that might be a number. The number is expected to be in the format of iiii.dddd The integer part and the decimal point are both optional.

Option Explicit
Function LargestNumberFromString(S As String) As Double
    Dim RE As Object, MC As Object, M As Object
    Dim D As Double
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "\b[0-9]*\.?[0-9]+\b"
    If RE.test(S) = True Then
        For Each M In MC
            D = IIf(D > CDbl(M), D, CDbl(M))
        Next M
    End If
End With
End Function

回答3:

This is going to be a rather complex answer for someone with no programming background, so be prepared to spend a lot of time covering and researching this topic if you really wish to achieve a function that finds the largest number in a string in excel.

The solution, requires the use of VBA and Regular expressions VBA is used in excel when there is a need for more complex functionality that just can't be achieved with the use of built in spreadsheet functions. Regular expressions are a language used to tell programs how to extract useful information from texts, in this case we can extract all the numbers in your text. with the following regular expression.

(\d+.?\d*)/g

Which roughly means: Match one or more digits with an optional period and subsequent optional digits. The program that will interpret this will do the following: Look for digits, if you see one, then that's a match, grab all contiguous digits and add them to the match. Once you find a character that is not a digit, start looking for new matches. if at any point you find a dot, add it to the match, but just once, and keep on looking for digits. Rinse and repeat until the end of the text.

You can test it here. In this case, the regex matches 19 numbers.

http://www.regextester.com/

Once you have a collection with the 19 matches (See link to regular expressions), all you would need to do is to loop over each of the matches to find out which of the numbers is the highest:

for each number in matches
    if number > highestNumber then
        highestNumber = number
    end if
next

And highestNumber will be the the result! In order to have this code run in a simple custom function, you can follow this microsoft tutorial ( https://support.office.com/en-us/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f?ui=en-US&rs=en-US&ad=US&fromAR=1 )

回答4:

Where c is your string to find max from

Dim qwe() As String
qwe = Split(c, ", ")
maxed = 0
For x = LBound(qwe) To UBound(qwe)
    qwe(x) = Left(qwe(x), InStr(1, qwe(x), " ", vbBinaryCompare))
    On Error Resume Next
    If CLng(qwe(x)) > maxed Then maxed = CLng(qwe(x))
Next x
    MsgBox maxed

The error line is there to ignore when qwe(x) cannot be converted to a LONG number.

I must say this is very specific to your string format, for a more comprehensive doodad you'd want to have the split delimiter as a variable and possibly use the "IsNumeric" function to scan the entire string.