SORTING HELP

VIRII1

New Member
Joined
Feb 18, 2004
Messages
7
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]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
yeah calvin,

forgot how spoiled i was with the new upgrade :cool:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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. :LOL:

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 18, 2004
Messages
7
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top