Numeric Sorting of text
Scott M Riddle
(Put all of the OP's in numeric order)
IE. OP20
OP50
OP150
OP200
OP210
What Excel currently does:
OP150
OP20
OP200
OP210
OP50
Thanks
Scott Riddle
David J. Braden
entered just as numbers (20, 50, etc.) and do the sort on that. To see tham
displayed with the OP prefix, custom format the cells:
Format/Cells, click number tab, click on custom, enter "OP"#. If this won't do
the trick, let us know.
Dave Braden
TMY Research
Scott M Riddle
with numbers at the end.
In article <375C1281...@fiastl.net>, t...@fiastl.net says...
Peter Beach
Sadly sorting alpha-numeric values is done on a purely left->right basis
using the ASCII code of each character. In the same way that Pat, Pata, Pe,
Pet, Peta, Peter is sorted correctly according to common-place usage, XL
will say that the order should be OP1, OP11, OP2, OP222222222, OP3 etc.
If you can transform your data into a sensible numeric sequence (perhaps
drop off the leading "OP" and then sort) you may be able to get the
behaviour you expect.
Depending on the structure of your alpha-numeric codes it may be easy to
turn them into a unique numeric variable, but you will need to provide more
details for anyone to work out what a sensible mapping might be.
Sorry not to be more help.
Regards,
Peter
Scott M Riddle wrote in message ...
Ogilvy, Thomas, W., Mr., ODCSLOG
numeric portion in the other. This formula will get the text portion
from cell C4:
=LEFT(C4,MIN(IF(ISNUMBER(MID(C4,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)
It is an array formula, so enter it with Ctrl+shift+enter
This formula will get the numeric portion and uses the results of the of
the above formula:
=RIGHT(C4,LEN(C4)-LEN(D4))*1
Adjust these to address your first row of data. Then select both cells
and double click in the lower right corner of the rightmost cell to have
the formulas fill down the column.
Then sort on these columns.
Also, in the first formula, you can replace the hard coded arrays with
row(indirect("1:6"))
=LEFT(C4,MIN(IF(ISNUMBER(MID(C4,ROW(INDIRECT("1:6")),1)*1),ROW(INDIRECT(
"1:6")),7))-1)
HTH,
Tom Ogilvy
-----Original Message-----
From: s...@ingersoll.com (Scott M Riddle ) [mailto:s...@ingersoll.com]
Posted At: Monday, June 07, 1999 4:02 PM
Posted To: misc
Conversation: Numeric Sorting of text
Subject: Re: Numeric Sorting of text
Some of the text is jst OP but there are some that are cbOP and cbBuf
with numbers at the end.
In article <375C1281...@fiastl.net>, t...@fiastl.net says...
> Are all the items prefixed by OP? If so, then one solution is to have
them
> entered just as numbers (20, 50, etc.) and do the sort on that. To
see tham
> displayed with the OP prefix, custom format the cells:
> Format/Cells, click number tab, click on custom, enter "OP"#. If this
won't do
> the trick, let us know.
>
> Dave Braden
> TMY Research
>
> Scott M Riddle wrote:
> >
Sean
I got some pretty good help from my first question to the group so I
thought I'd try to return the favor.
I work with part numbers not unlike the OP#### format you mentioned so
I came up with an alphanumeric sort macro.
It only works with alphanumerics in the following 4 formats:
Text Number Text i.e. abc123abc
Text Number i.e abc123
Number Text i.e. 123abc
Number i.e. 123
But they can be mixed in a row or col.
I also found that if the alphanumerics contain leading zeros within
the number, they are lost during the sort, but adding another array to
keep track of the zeros should be possible. I havent tried this yet.
Back up your sheet and try it out.
Just highlight the col or row you want to sort then run the macro.
I'm really just getting the hang of VBA so there is no error handling
in the macro.
Donations in the form of constructive critisicm are accepted.
But, if anyone feels the need to flame, please be gentle as this is my
first post to the group :)
Option Explicit
Sub OP_Sort()
Dim Cell
Dim OP_Pre_List()
Dim OP_Num_List() As Long
Dim OP_Suf_List()
Dim Where_List() As Long
Dim OP_Select As Range
Dim Cnt As Integer
Set OP_Select = Selection
ReDim OP_Pre_List(1 To OP_Select.Count)
ReDim OP_Num_List(1 To OP_Select.Count) As Long
ReDim OP_Suf_List(1 To OP_Select.Count)
ReDim Where_List(1 To OP_Select.Count) As Long
Cnt = 1
For Cnt = 1 To OP_Select.Count
Where_List(Cnt) = Cnt
Next Cnt
Application.ScreenUpdating = False
Cnt = 1
For Each Cell In OP_Select
OP_Pre_List(Cnt) = Get_Prefix(Cell.Value)
OP_Num_List(Cnt) = Get_Numeral(Cell.Value)
OP_Suf_List(Cnt) = Get_Suffix(Cell.Value)
Cnt = Cnt + 1
Next Cell
Call BubbleSort(OP_Num_List, Where_List)
Cnt = 1
For Each Cell In OP_Select
Cell.Value = OP_Pre_List(Where_List(Cnt)) & OP_Num_List(Cnt) _
& OP_Suf_List(Where_List(Cnt))
Cnt = Cnt + 1
Next Cell
Application.ScreenUpdating = True
End Sub
Public Function Get_Prefix(OP_String As String)
' Extract the alphabetic Prefix from current cell.
' If the first alphanumeric is a number,
' set the current array position to ""
' and Exit Function
Dim Cnt As Integer
If IsNumeric(Left(OP_String, 1)) Then
Get_Prefix = ""
Exit Function
Else
For Cnt = 1 To Len(OP_String)
If IsNumeric(Mid(OP_String, Cnt, 1)) Then
Exit For
End If
Next Cnt
Get_Prefix = Left(OP_String, (Cnt - 1))
End If
End Function
Public Function Get_Numeral(OP_String As String) As Long
'Extract the number from the current cell.
Dim Cnt1 As Integer
Dim Cnt2 As Integer
'Find the position of the first number
For Cnt1 = 1 To Len(OP_String)
If IsNumeric(Mid(OP_String, Cnt1, 1)) Then
Exit For
End If
Next Cnt1
'Find the number of digits in the number
For Cnt2 = Cnt1 To Len(OP_String)
If (IsNumeric(Mid(OP_String, Cnt2, 1))) = False Then
Exit For
End If
Next Cnt2
Get_Numeral = Mid(OP_String, Cnt1, (Cnt2 - Cnt1))
End Function
Public Function Get_Suffix(OP_String As String)
'Extract the alphabetic Suffix from the current cell.
Dim Cnt1 As Integer
'If no suffix exist Exit Function
If IsNumeric(Right(OP_String, 1)) Then
Get_Suffix = ""
Exit Function
End If
'Find the position of the last number in the string.
For Cnt1 = Len(OP_String) To 1 Step -1
If IsNumeric(Mid(OP_String, Cnt1, 1)) Then
Exit For
End If
Next Cnt1
Get_Suffix = Right(OP_String, (Len(OP_String) - Cnt1))
End Function
Sub BubbleSort(List() As Long, List2() As Long)
'Sort the OP_Num_List Array, keeping track of
'the original Number position using the Where_List
'Array so the Prefix and Suffix can be matched
'back to the Number.
'Based on an example from "Excel for Windows95 Power
'Programming with VBA" 2nd Edition
'By John Walkenbach
Dim First As Integer, Last As Integer
Dim i As Long, j As Long
Dim Temp As Long
Dim Temp2 As Long
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
Temp2 = List2(j)
List(j) = List(i)
List2(j) = List2(i)
List(i) = Temp
List2(i) = Temp2
End If
Next j
Next i
End Sub
Sean E. Dolan
s_d...@NOSPAMbellsouth.net