Sorting Street Numbers
 

I'm using Excel 2007. How would I sort the following:

1111th
131st
1st
223rd
2nd
3rd
65th

to get:

1st
2nd
3rd
65th
131st
223rd
1111th

Sorting Street Numbers
 

One play to try on a copy of your data sheet
(Tested fine here in xl2003)

Assume data as posted in col A
a. Insert a new col B (if reqd). Copy n paste col A into col B.
b. Install* & Run** Gord's sub below on col B to remove all alphas
c. Select the entire table, do a Data Sort Sort by col B, Ascending

*To Install:
Press Alt+F11 to go to VBE. Click InsertModule. Copy n paste Gord's sub
below into the code window (whitespace on the right). Press Alt+Q to get back
to Excel.

**To Run:
Select col B (or select the data range in col B). Press Alt+F8 to bring up
the macro dialog. Select "RemoveAlphas" Run (or just double-click on
"RemoveAlphas")

'-------
Sub RemoveAlphas()
' Gord Dibben
'' Remove alpha characters from a string
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9,.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub
'-------

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"BASR52" wrote:
I'm using Excel 2007. How would I sort the following:

1111th
131st
1st
223rd
2nd
3rd
65th

to get:

1st
2nd
3rd
65th
131st
223rd
1111th

Sorting Street Numbers
 

Here's an alternative sub by Harlan which also removes the alphas:

Sub DeleteAlphas()
' Harlan
Dim rng As Range, c As Range, re As Object

Set rng = Selection.SpecialCells( _
Type:=xlCellTypeConstants, _
Value:=xlTextValues _
)

Set re = CreateObject("vbscript.regexp")
re.Pattern = "[^0-9.]+" 'or use an InputBox to set
re.Global = True

For Each c In rng
c.Formula = re.Replace(c.Formula, "")
Next c
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

Sorting Street Numbers
 

This is an excellent idea! I never thought about having an additional column
minus the alpha characters to sort by. Thanks!

"Max" wrote:

One play to try on a copy of your data sheet
(Tested fine here in xl2003)

Assume data as posted in col A
a. Insert a new col B (if reqd). Copy n paste col A into col B.
b. Install* & Run** Gord's sub below on col B to remove all alphas
c. Select the entire table, do a Data Sort Sort by col B, Ascending

*To Install:
Press Alt+F11 to go to VBE. Click InsertModule. Copy n paste Gord's sub
below into the code window (whitespace on the right). Press Alt+Q to get back
to Excel.

**To Run:
Select col B (or select the data range in col B). Press Alt+F8 to bring up
the macro dialog. Select "RemoveAlphas" Run (or just double-click on
"RemoveAlphas")

'-------
Sub RemoveAlphas()
' Gord Dibben
'' Remove alpha characters from a string
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9,.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub
'-------

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"BASR52" wrote:
I'm using Excel 2007. How would I sort the following:

1111th
131st
1st
223rd
2nd
3rd
65th

to get:

1st
2nd
3rd
65th
131st
223rd
1111th

Sorting Street Numbers
 

Welcome, glad it got you going.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"BASR52" wrote in message
...
This is an excellent idea! I never thought about having an additional
column
minus the alpha characters to sort by. Thanks!