How to sort a column with a range in it...

[nniedzielski]

I was given a large data file and one of the columns has a mileage range in it:

by default, Excel is treating it like text, and i cannot figure out how to get excel to sort the column the common sense way which would be:
0-20
21-40
and so on and so on

I tried google and tried changing the values to number, and general, but nothing seems to work, and i do not want to have to go through and split the range up into multiple cells unless i absolutely have to.

 

[kevin9999]

If you're open to a VBA solution then the following seems to work. Obviously I have no idea what your data looks like (columns involved, range, sheet name etc.) but if you provide a sample using the XL2BB add in then I'll happily adjust the demonstration code below.
With this code:

Option Explicit
Sub TestSort()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    Dim LRow As Long, LCol As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    
    With ws.Range(ws.Cells(2, LCol), ws.Cells(LRow, LCol))
        .value = _
        Evaluate("IFERROR(INT(MID(" & .Offset(, -3).Address & ",1,FIND(""-""," _
        & .Offset(, -3).Address & ")-1)),2000)")
    End With
    
    ws.Range("A1").CurrentRegion.Sort key1:=ws.Cells(1, LCol), Order1:=xlAscending, Header:=xlYes
    ws.Cells(1, LCol).EntireColumn.ClearContents
    Application.ScreenUpdating = True
End Sub

This:

Book1
	A	B	C
1	header1	header2	header3
2	161-180	was row 2	was row 2
3	2000+	was row 3	was row 3
4	1501-2000	was row 4	was row 4
5	0-20	was row 5	was row 5
6	21-40	was row 6	was row 6
7	181-200	was row 7	was row 7
8	1001-1500	was row 8	was row 8
Sheet1

Becomes this:

Book1
	A	B	C
1	header1	header2	header3
2	0-20	was row 5	was row 5
3	21-40	was row 6	was row 6
4	161-180	was row 2	was row 2
5	181-200	was row 7	was row 7
6	1001-1500	was row 8	was row 8
7	1501-2000	was row 4	was row 4
8	2000+	was row 3	was row 3
Sheet1

 

[Peter_SSs]

Excel is treating it like text

That isn't surprising as the values are text. After all you cannot sum them, find an average etc.

i do not want to have to go through and split the range up into multiple cells unless i absolutely have to.

To me that would be a simple way to go as you only need one column not multiple.
For example this, then sort by the extra column.

22 12 06.xlsm
	A	B	C	D	E
1	Hdr1	mileage	Hdr3	Hdr5	Sort By
2		101-120		data 1	101
3		161-180		data 2	161
4		1001-1500		data 3	1001
5		2001+		data 4	2001
6		201-220		data 5	201
Sort

Cell Formulas
Range	Formula
E2:E6	E2	=LEFT(B2,FIND("-",SUBSTITUTE(B2,"+","-"))-1)+0