SORTING HELP

[VIRII1]

how would i sort by numeric value?
if this was my column?

c3
c4p
c4398
c46p
c7
c71
c7134
c72p

i would like the end result to look like this:

c3
c4p
c46p
c4398
c7
c71
c72p
c7134[/i]

 

[Zack Barresse]

i'm using xl xp, and if i put your figures into a column, and go to Data -> Sort -> Ascending, it comes out just fine.

does this not work for you??

 

[Cbrine]

Zack,
I tried it in XL97 and got different results. I don't know an easy solution for this problem, other than upgrade to XP.
c3
c4398
c46p
c4p
c7
c71
c7134
c72p

 

[Zack Barresse]

yeah calvin,

forgot how spoiled i was with the new upgrade

 

[Joe4]

Here is a possible workaround that works for the data you provided.

Let's say that your data is in the Range A1:A8. In B1, enter this formula:
=MID(A1,2,1)&LEN(A1)
and copy down for all rows.

Now sort your data, primary sort key column B and secondary sort key column A.

You can hide this column, if desired.

 

[Cbrine]

jmiskey,
That will work if length is the only other factor involved, but it may not work if the values are the same length with different text or numeric values. From the guy had doesn't have any solution, of course.

Zack,
I should be upgrading to XP too. The only thing is, that will happen when they release XP2010.

 

[Joe4]

jmiskey,
That will work if length is the only other factor involved, but it may not work if the values are the same length with different text or numeric values.

I agree, that is why I prefaced it with:

Here is a possible workaround that works for the data you provided.

It should work if they all start with "c" and then are followed by the number. If there are other conditions/possibilites, they need to identify them, and then we can modify/create the solution from there.

 

[VIRII1]

ok, now i must have a column to change the data by numbers only
if i have

c3
c4p
c4398
c46p
c7
c71
c7134
c72p

then i have to have a column like this:

c3
c4p
c7
c46p
c71
c72p
c4398
c7134

 

[Joe4]

OK, I approached this a little differently. I wrote a custom function which pulls out the numerical value of your string. You can then sort by this value first, then your string.

Here is the UDF to paste into your VBA code box:

Function StripNumbers(myValue) As Long

    Dim i As Integer
    For i = 1 To Len(myValue)
        If IsNumeric(Mid(myValue, i, 1)) Then
            StripNumbers = StripNumbers & Mid(myValue, i, 1)
        End If
    Next i
    
End Function

Then use it like any other function. For example, to strip out the number out of A1, enter:

=StripNumbers(A1)