Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate address numbers from street name | Excel Worksheet Functions | |||
How do you remove the numbers from a street address in EXCELL? | Excel Worksheet Functions | |||
Sorting street addresses | Excel Discussion (Misc queries) | |||
how do I convert cell 'Street,#' to '# Street' these are addresse | Excel Worksheet Functions | |||
How do I sort a column of street number/street name by the stree. | Excel Worksheet Functions |