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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
66,494
Office Version
  1. 365
Platform
  1. Windows
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
66,494
Office Version
  1. 365
Platform
  1. Windows
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
66,494
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)
 

Forum statistics

Threads
1,186,163
Messages
5,956,297
Members
438,246
Latest member
Deelea

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