Sort by number in text

[Susan]

I would like to sort a worksheet containing data on our
products by product name. The problem is the way that I
want to sort it is by the number part of the product
name. For example a product name might be Susan's R4
Oil. I want to sort on the number 4. There are varying
numbers of characters on each side of the number.

Can anyone suggest a way to do this?

Thanks.

Susan

 

[Don Guillett]

Put this code I found in the archives in a regular module by itself

Function GetValue(str)
Dim n As Integer, i As String
i = ""
For n = 1 To Len(str)
If IsNumeric(Mid(str, n, 1)) Then
i = i & Mid(str, n, 1)
If Mid(str, n + 1, 1) = "." Then i = i & "."
End If
Next
If i = "" Then
GetValue = i
Exit Function
End If
GetValue = CDbl(i)
End Function

Then =getvalue(a1) and copy down. Sort on that.

 

[jr]

I take it the number portion can be of any length (e.g.
1, 123, 1234, etc.) and in any position (a1bb, ab12cc,
abc123ddd, etc.) ??

 

[Susan]

OK, it worked great, except for one thing that I didn't
mention before. Some of our products have a container
size as part of the product name. How can I modify the
code so it only looks at the first number in the text
string?

Thanks.

Susan

 

[Don Guillett]

this should do it

Function GetleftValue(str)
Dim n As Integer, i As String
i = ""
For n = 1 To Len(str)
If IsNumeric(Mid(str, n, 1)) Then
i = i & Mid(str, n, 1)
If Mid(str, n + 1, 1) = "." Then i = i & "."
End If
Next
If i = "" Then
GetleftValue = i
Exit Function
End If
GetleftValue = Val(Left(CDbl(i), 1))
End Function

 

[Harlan Grove]

I would like to sort a worksheet containing data on our
products by product name. The problem is the way that I
want to sort it is by the number part of the product
name. For example a product name might be Susan's R4
Oil. I want to sort on the number 4. There are varying
numbers of characters on each side of the number.

This sort of request has appeared from time to time in the newsgroup. Usually
UDFs are offered as solutions. I had to see if this could be done solely with
built-in functions. It can, but it requires using a defined name, which I'll
call Seq, referring to something like =ROW(INDIRECT("1:1024")) . I'll also
assume your text is in a cell named X. Then try the array formula

=--MID(X,MATCH(-1,-ISNUMBER(-MID(X,Seq,1)),0),MATCH(0,-ISNUMBER(-MID(MID(X,
MATCH(-1,-ISNUMBER(-MID(X,Seq,1)),0)+1,1024),Seq,2)),0)+1)

This extracts the leftmost longest digit string possibly including decimal point
but not sign characters.