Sorting By Largest Value In A String
 

I would like to find the largest value, in a string of values in a
cell. The, using that information, i would like to sort the rows in a
worksheet by that largest value.

A representative cell contains characters like this:

141, 141, 130, 130, 108 and 108

For instance, I woulld like for it to say 141 is the largest value.
Then, it should sort the rows containing these cells by that largest
value.

Can anyone give me a function, or program, that would allow me to do
this?

Thanks,
Alan

Sorting By Largest Value In A String
 

use a helper column

Create a function like
Public Function BiggestNumber(rng As Range) As Variant
Dim s As String, v As Variant
Dim i As Long, Max As Long
If rng.Count 1 Then
BiggestNumber = CVErr(xlErrRef)
Exit Function
End If
s = LCase(rng.Text)
s = Replace(s, " and ", ",")
s = Replace(s, " ", "")
v = Split(s, ",")
Max = -10000
For i = LBound(v) To UBound(v)

If IsNumeric(v(i)) Then
If CLng(v(i)) Max Then _
Max = CLng(v(i))
End If
Next
BiggestNumber = CDbl(Max)
End Function

Put it in a general module (not a sheet module or the thisworkbook module)

(assume the string of numbers is in column B)
then in another cell in the same row put in a formula like

=BiggestNumber(B1)

then drag fill down the column

Now sort with this helper column as the Key field.

--
Regards,
Tom Ogilvy





" wrote:

I would like to find the largest value, in a string of values in a
cell. The, using that information, i would like to sort the rows in a
worksheet by that largest value.

A representative cell contains characters like this:

141, 141, 130, 130, 108 and 108

For instance, I woulld like for it to say 141 is the largest value.
Then, it should sort the rows containing these cells by that largest
value.

Can anyone give me a function, or program, that would allow me to do
this?

Thanks,
Alan