Excel 2007在文本字符串中找到最大的数字 [英] Excel 2007 find the largest number in text string

问题描述

我有Excel2007.我试图在包含以下内容的单元格中找到最大的数字:

[[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]] p

我不是编码员，但是我可以通过一些基本的说明来解决.如果有一个公式可以做到这一点，那就太好了！如果最好的方法是某种后端代码，请告诉我.谢谢您的时间.

我确实具有以下公式，几乎可以将我带到那里:

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

对于包含上述字符串的单元格，它将起作用.但是，使用包含类似以下内容的字符串:

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

我最终将19198934678的值作为最大值.

解决方案

您可以使用以下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

将此内容放入工作簿附带的模块中.在工作表或ThisWorkbook代码中不.

然后您可以像其他任何公式一样调用它:

= MaxInString(A1)

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.

解决方案

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)

这篇关于Excel 2007在文本字符串中找到最大的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！